kamal rumba kamal rumba - 8 days ago 7
MySQL Question

slow query even with the index on large table

I am performing a simple select query to extract username from table logs(containing 54864 rows).
It took about 7.836s to retrieve data.
How can I speed up the performace???

SELECT username FROM `logs`
WHERE
logs.branch=1
and
logs.added_on > '2016-11-27 00:00:00'


On describing table,

+-------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | char(255) | YES | MUL | NULL | |
| fullname | char(255) | YES | | NULL | |
| package | char(255) | YES | | NULL | |
| prev_expiry | date | YES | | NULL | |
| recharged_upto | date | YES | | NULL | |
| payment_option | int(11) | YES | MUL | NULL | |
| amount | float(14,2) | YES | | NULL | |
| branch | int(11) | YES | MUL | NULL | |
| added_by | int(11) | YES | | NULL | |
| added_on | datetime | YES | MUL | NULL | |
| remark | text | YES | | NULL | |
| payment_mode | char(255) | YES | | NULL | |
| recharge_duration | char(255) | YES | | NULL | |
| invoice_number | char(255) | YES | | NULL | |
| cheque_no | char(255) | YES | | NULL | |
| bank_name | char(255) | YES | | NULL | |
| verify_by_ac | int(11) | YES | | 0 | |
| adjusted_days | int(11) | YES | | NULL | |
| adjustment_note | text | YES | | NULL | |
+-------------------+-------------+------+-----+---------+----------------+
20 rows in set


On explaining query,

+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | logs | ref | branch_index,added_on_index | branch_index | 5 | const | 37 | Using where |
+----+-------------+--------------------------+------+-----------------------------+--------------+---------+-------+------+-------------+
1 row in set


updated:: explaing query after adding composite index(branch_added_index )

+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
| 1 | SIMPLE | logs | ref | branch_index,added_on_index,branch_added_index | branch_index | 5 | const | 37 | Using where |
+----+-------------+--------------------------+------+------------------------------------------------+--------------+---------+-------+------+-------------+
1 row in set

Answer

Add a composite key on branch,added_on so you cover all the WHERE conditions since you use AND.

ALTER TABLE logs ADD KEY(branch,added_on)

This should be much faster,also you can drop the branch_index key since the above index can replace it.You only return 37 rows from 54000 so the cardinality is OK.

ALTER TABLE logs DROP INDEX `branch_index`;

Or you can use index hints

SELECT username FROM `logs` USE INDEX (branch_added_index)  WHERE 
logs.branch=1 
and
logs.added_on > '2016-11-27 00:00:00'
Comments