Solved

MySQL query not using index

Posted on 2014-01-18
9
441 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Creating and Managing Databases with phpMyAdmin in cPanel.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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