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

The following is part of a larger PHP script that adds or disconnects users from a MySQL database using a CSV file and a client ID as input.

There are two tables, users and users_clients . Later, communication is maintained between users and clients, since a user can belong to several clients.


This is a table structure

Structure

users (she 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 main script flow for adding each user from a CSV file:

  • , .

    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
    
  • , .

    INSERT INTO 
        users ($fields,id_client)
    VALUES 
        ($values,'$id_operation')
    
  • . , - mysql_insert_id, ?

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

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

400.000 . script 10 + 5 CVS 500 .

, ?

.

PD: , pastebin.

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

: id !!!

+6

INSERT , mysql_insert_id() , .

.

+2

All Articles