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)
SEARCH | user | FULLTEXT SEARCH | name | FULLTEXT SEARCH | email | FULLTEXT
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:
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
If it doesn't exist, add it to the database.
INSERT INTO users ($fields,id_client) VALUES ($values,'$id_operation')
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'
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.
INSERT INTO table (id,a,b,c) VALUES (5454,1,2,3) ON DUPLICATE KEY UPDATE table SET foo WHERE id=xyz;
- Set indexes in the DB
- use mysqli instead of mysql
- 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
- 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
- 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.