slow query even with the index on large table

Question

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

Mihai · Accepted Answer · 2016-11-27 10:34:30Z

2

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'

edited Nov 27, 2016 at 10:34

answered Nov 27, 2016 at 10:08

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Kamal Lama Over a year ago

I have created the composite index as you suggested but still query execution time is not improved

Mihai Over a year ago

@kamalrumba Add the new explain and drop the old index

Mihai Over a year ago

@kamalrumba From the new explain I see the query still uses the old index.Just drop it

Kamal Lama Over a year ago

I have dropped the index as you suggested and query execution time improved considerably. Can you please explain why separately created indexes on the same column is slower then the composited index.

Mihai Over a year ago

@kamalrumba In short, the new index has better cardinality, it can take into account both conditions at the same time while the old index selected all logs.branch=1 and after that it scanned the other column.

sherpaurgen · Accepted Answer · 2016-11-27 10:14:32Z

2

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)

answered Nov 27, 2016 at 10:14

sherpaurgen

3,3326 gold badges37 silver badges49 bronze badges

1 Comment

Strawberry Over a year ago

I think we've got the message

Bernd Buffen · Accepted Answer · 2016-11-27 10:09:31Z

1

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

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

answered Nov 27, 2016 at 10:09

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

Comments

Rick James · Accepted Answer · 2016-11-28 02:04:34Z

1

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.

answered Nov 28, 2016 at 2:04

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

slow query even with the index on large table

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related