MySQL Forums
Forum List  »  Performance

Re: Queries returning large number of rows
Posted by: Anthony Willard
Date: May 23, 2006 07:13AM

Is your "like 'whatever%'" clause large enough? Using too few characters may make the query engine decide to ignore the index.

For instance, 'abc%' is more generic the 'abcdefgh%'. If your data has many rows beginning with 'abc', the optimizer may not choose the index over a table scan.

Also, you may see better performance for a minimally represented prefix, and poorer performance for a more common prefix.

This is a common issue that arises with the "like" clause.

Any index you create (even one with maximum cardinality) won't help much if you ask it to process millions of records. If all of your data is available in your index, the scan takes place against the index instead of the table itself, but it's still a scan.

With that in mind, you might try reducing the prefixes in your index to increase the key density. (Do you really need 62 bytes if you use fewer than that?) This may yield a modest performance gain. But again, counting many rows takes time.

I hope this helps.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Queries returning large number of rows
1747
May 23, 2006 07:13AM


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.