MySQL Forums
Forum List  »  Performance

Re: Queries returning large number of rows
Posted by: Anthony Willard
Date: May 24, 2006 10:18AM

I'm running v5.0 and haven't explored the merge index features. I've seen similar features on other DBMS's but wasn't convinced they are as good as well-conceived composite indexes. Since you are using v5.1, maybe you should look into the table partitioning. This would distribute your data across multiple tables and if you partitioned them well, any processing would only access the partition containing your data and provide an improvment.

Yes, you can have multiple indexes using the same basic keys. It may be worth it to try it to see if it works. There's theory and then there's reality.

The trick is to not overwhelm the inserts and updates which must update the indexes as well. So, having more indexes will slow inserts and updates, but if they can provide efficient select actions, then the payoff may be worth it.

Another trick you might try is to reverse the keys (previous, current) (in addition to a single "current" index). Sometimes, the previous value is just more distictive and reversing the order will provide a better target when searching. Here you must understand your data to know if it is actually worth a look.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Queries returning large number of rows
1645
May 24, 2006 10:18AM


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.