MySQL query not using index

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
LVL 1
fkoyerAsked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
fkoyerAuthor Commented:
Thomas,

I tried your suggestion and did analyze table and optimize table but the explain output still says "Using where"
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
Tomas Helgi JohannssonCommented:
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
 
fkoyerAuthor Commented:
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
 
fkoyerAuthor Commented:
I did add the other index you suggested. FYI
0
 
Tomas Helgi JohannssonCommented:
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
 
fkoyerAuthor Commented:
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
 
fkoyerAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.