How can I improve the performance of this PHP/MySQL code?

Question

The following is a part of a bigger PHP script which adds or disables users from a MySQL database using a CSV file and a client id field as input.

There are two tables involved, users and users_clients. The later keeps the relationship between users and clients as an user can belong to multiple clients.

This is the structure of the tables

users structure (it has more fields)

id        | int(11) (primary key)
user      | varchar(100)
pass      | varchar(100)
category  | int(11)
date      | timestamp
name      | varchar(100)
email     | varchar(255)

users indexes

SEARCH    | user        | FULLTEXT
SEARCH    | name        | FULLTEXT
SEARCH    | email       | FULLTEXT

users_clients structure

id_user   | int(11)
id_client | int(11)
status    | enum('active','inactive')

This is the basic flow of the script for adding each user from the CSV file:

1. Check if the user exists for that client.

   SELECT 
       LOWER(user)
   FROM
       users u
       INNER JOIN users_clients uc ON u.id = uc.id_user
   WHERE
       u.user = '$user'
       and uc.id_client = $id_client
   

2. If it doesn't exist, add it to the database.

   INSERT INTO 
       users ($fields,id_client)
   VALUES 
       ($values,'$id_operation')
   

3. Get the id of the inserted user. I know I could use something like mysql_insert_id here, but what about the race conditions?.

   SELECT
       u.id as id
   FROM
       users u
   WHERE
       u.user = '$user'
       and u.id_client = '$id_operation'
   

4. Associate the user with the corresponding client.

   INSERT INTO
       users_clients (id_user, id_client) 
   VALUES
       ('$id_user','$id_client')
   

There are currently 400.000 users in the table. The script takes 10+ minutes to process a CVS with 500 users.

How would you improve this so that it is faster?

Thanks in advance.

PD: If you want to see the complete function, it's available at pastebin.

Answer 1

INSERT INTO table (id,a,b,c) VALUES (5454,1,2,3)
ON DUPLICATE KEY
UPDATE table SET foo WHERE id=xyz;

1. Set indexes in the DB
2. use mysqli instead of mysql
3. collect all the stuff you want to insert and do it with a prepared statement / stored procedure like here How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
4. don't do 500 SELECTs, simple get the entire database and work through it via a foreach/while loop, checking for the stuff you need
5. use a construct like above

Important: For the above statement the column id should have an unique index !!!

Answer 2

Wrap INSERTs into transaction and don't worry, mysql_insert_id() is completely safe unless you switch to another database connection.

It is also possible to wrap all your queries to a transaction in result of massive speed improvement.