Solved

MySQL query not using index

Posted on 2014-01-18
9
443 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

756 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