MySQL Forums
Forum List  »  Optimizer & Parser

Re: How to speed up query of indexed column with 5M rows?
Posted by: Toa Sty
Date: October 09, 2006 09:40AM

Hi Ryan,

Agree with most of what Larry said, just want to add:

You have an index on owner, but the explain plan says that MySQL isn't going to use it, instead it's going to table scan. Given that the particular id you're looking for doesn't even exist this is obviously a big waste of time. MySQL/MyISAM would normally know that no rows match just from the index AFAIK.

About the only time I've ever seen MySQL do something like that is when index stats are very out of date, so try running
 ANALYZE TABLE flow; 
and re-run the explain.

(With a 5M row table it might take a while, and it read-locks MyISAM tables so be careful if you need to be!)

-----------------------------------------
email: 'toasty'*3 at gmail



Edited 1 time(s). Last edit at 10/09/2006 12:56PM by Toa Sty.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to speed up query of indexed column with 5M rows?
6542
October 09, 2006 09:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.