Re: Query Optimization Needed

[Devrishi Shandilya]

Hi Peter,

Thanks for your great help.

Yes we get some boost with covering index. Before covering index, query was not using index but after we created it, it using covering index.

I run the query with or without covering index and found in slow_logs that Rows_exmanined are same in both cases, But in explain result it showing me that with covering index, rows_exmanined are just half than with out covering index. Pl suggest on this.

Is there other ways to get more performance?

Please go through it :

with out covering index :

# Time: 150212 9:51:33
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 4.211973 Lock_time: 0.343198 Rows_sent: 168682 Rows_examined: 603953
use rockrest;
SET timestamp=1423714893;
SELECT DISTINCT `userdeviceinfo`.`deviceid` FROM `userdeviceinfo`
WHERE (`userdeviceinfo`.`created_on` < '2015-02-10 18:30:00' AND NOT (`userdeviceinfo`.`user_id` IS NULL));

--------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+------+-------------------------+------+---------+------+--------+------------------------------+
| 1 | SIMPLE | userdeviceinfo | ALL | userdeviceinfo_fbfc09f1 | NULL | NULL | NULL | 451857 | Using where; Using temporary |
+----+-------------+----------------+------+-------------------------+------+---------+------+--------+------------------------------+


with covering index :

# Time: 150212 9:52:18
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 1.747189 Lock_time: 0.000000 Rows_sent: 168682 Rows_examined: 602494
SET timestamp=1423714938;
SELECT DISTINCT `userdeviceinfo`.`deviceid` FROM `userdeviceinfo`
WHERE (`userdeviceinfo`.`created_on` < '2015-02-10 18:30:00' AND NOT (`userdeviceinfo`.`user_id` IS NULL));

--------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------+-------+-----------------------------+------+---------+------+--------+-------------------------------------------+
| 1 | SIMPLE | userdeviceinfo | range | userdeviceinfo_fbfc09f1,kkk | kkk | 8 | NULL | 225928 | Using where; Using index; Using temporary |
+----+-------------+----------------+-------+-----------------------------+------+---------+------+--------+-------------------------------------------+


Regards,
Devrishi