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

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.

Answers


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 !!!


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.


Need Your Help

How to build iOS7 Style Audio Recorder App

uiviewcontroller ios7 audio-recording avaudiorecorder audiorecord

I am trying to build an audio recorder app similar to iOS7 built in one and looking for guidance on what controls to use for the recording app. I understand I will be using a tableview for the list...

Vista/7 UAC: how to lower process privileges

c# uac windows-vista samba

Is it possible for a process to lower itself from elevated UAC permission back to standard user? If not can the elevated process launch its copy with standard user token and then kill itself? Any c...