Solved

MySQL query not using index

Posted on 2014-01-18
9
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 25

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 25

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 25

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 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 500 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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