CODEWITHSUNDEEP
mysql
Kamal Lama
Kamal Lama

Reputation: 700

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

Upvotes: 3

Views: 1140

Answers (4)

Rick James
Rick James

Reputation: 142278

This will be even faster, because it is "covering":

INDEX(branch, added_on, username) -- in exactly that order.

(And drop any indexes that are prefixes of this.)

Index Cookbook

"Cardinality" is rarely of importance. And EXPLAIN often gets the value wrong.

The EXPLAIN shows 5 for the size of branch -- does it really need to be NULLable? Will you have 2 billion branches? Consider using something smaller, such as the 1-byte TINYINT UNSIGNED NOT NULL (values 0..255).

Also, shrink the 255 to something reasonable.

When describing a table, please use SHOW CREATE TABLE; it is more descriptive. It might help to know the Engine, Charset, etc.

Upvotes: 1

Mihai
Mihai

Reputation: 26784

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'

Upvotes: 2

sherpaurgen
sherpaurgen

Reputation: 3274

if your table's existing index is already used for other queries try adding new composite index like below create index <indexname> on logs(branch,added_on)

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

create a composite index on 2 fields (branch, added_on) like:

ALTER TABLE `logs` ADD KEY idx_branch_added (branch, added_on);

Upvotes: 1

Related Questions