MySQL Forums
Forum List  »  Performance

Re: Query Optimization Needed
Posted by: Devrishi Shandilya
Date: February 12, 2015 03:51AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1666
February 11, 2015 01:31AM
750
February 11, 2015 12:42PM
Re: Query Optimization Needed
853
February 12, 2015 03:51AM
783
February 12, 2015 01:24PM
749
February 13, 2015 06:53AM
682
February 13, 2015 07:36AM
702
February 14, 2015 01:04AM
780
February 15, 2015 11:07PM
793
February 16, 2015 12:15AM
658
March 13, 2015 11:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.