[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL query not using index

Posted on 2014-01-18
9
Medium Priority
?
451 Views
Last Modified: 2014-01-18
have an index on one of my tables that contains 3 integer fields like this:

msgrcpt_idx_domain_id (domain_id, quar_flag, time_reverse)

In my query, I'm trying to select by the first 2 fields and sort by the 3rd. It seems like it should be able to use the index for this and be very fast. However, when I do a simple query using these 3 fields, it takes a longer than expected and the EXPLAIN output says "Using where" instead of "Using index".

explain select * from msgrcpt where domain_id = 117 and quar_flag = 0 order by time_reverse;

+----+-------------+---------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+-----------------------+-----------------------+---------+-------------+------+-------------+
| 1 | SIMPLE | msgrcpt | ref | msgrcpt_idx_domain_id | msgrcpt_idx_domain_id | 5 | const,const | 9250 | Using where |
+----+-------------+---------+------+-----------------------+-----------------------+---------+-------------+------+-------------+

Open in new window


Any ideas what I'm doing wrong?

Thanks
Kent
0
Comment
Question by:fkoyer
  • 5
  • 4
9 Comments
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39790809
Hi!

If you look closer to the output of the explain then you will see that it is choosing the index.

After creating the index did you run analyze table and/or optimize table ?
If not do that and reexecute the explain.

You could also create this index and do the above (optimize/analyze table)
and see if that speeds the query

msgrcpt_idx_quar_flag (quar_flag,domain_id, time_reverse)


Regards,
    Tomas Helgi
0
 
LVL 1

Author Comment

by:fkoyer
ID: 39790828
Thomas,

I tried your suggestion and did analyze table and optimize table but the explain output still says "Using where"
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39790832
Try the other index I just added into the comment. :)

Depending on your column statistics that index could be better in your case.

Regards,
    Tomas Helgi
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 1

Author Comment

by:fkoyer
ID: 39790902
Perhaps I am misunderstanding the output from EXPLAIN. If I change my selection clause to only include columns from the index, like this:

select domain_id from msgrcpt where domain_id = 117 and quar_flag = 0 order by time_reverse

Then the EXPLAIN output says "Using where; using index". But if I include other columns (or use SELECT *) then it just says "Using where"

Does this make sense?
0
 
LVL 1

Author Comment

by:fkoyer
ID: 39790913
I did add the other index you suggested. FYI
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39790916
Hi!

The "using where,using index" means in a short words that it's an index only query.
Your first explain says that it's using the the index and matcthing it to the where clause columns of your query.

How much data is in your table ?
Execution time of your query depends on the where clause of your query, the amount of data, table definition and it's  indexes and the column statistics. MySQL uses that to find the optimal access path.

Regards,
     Tomas Helgi
0
 
LVL 1

Author Comment

by:fkoyer
ID: 39790953
This particular table has 1.8 million records. It is an email database and we write to the database every time an email is received which is about every 2 seconds on average.

I wonder if the index is not the problem. Perhaps it is taking a long time to access the rows. How would I determine if this is the case.
0
 
LVL 26

Accepted Solution

by:
Tomas Helgi Johannsson earned 2000 total points
ID: 39791019
Hi!

Are you running MySQL on Linux/Unix ?
If so you should setup mysqltuner and run it to see if you need to tune buffers and other parameters.

http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner

Regards,
    Tomas Helgi
0
 
LVL 1

Author Comment

by:fkoyer
ID: 39791086
It is an Amazon RDS instance.

Thanks for the tool. I ran it and it suggested adding RAM before increasing buffer size. So I guess I just need to pay for a larger instance on Amazon.

I think the output from EXPLAIN was just misleading. Even though it says "Using where" it is still using the index. (I think)

Thanks for the help. I will mark it a solved.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 4 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question