Re: Queries returning large number of rows
Anthony Willard wrote:
> 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, I actually started exploring this yesterday. Unfortunately I ran into a bug (acknowledged by one of the MySQL guys), fixed in the 5.1.10 release which isn't out yet. Specifically my autoincrementing id primary key field failed to autoincrement as soon as I switched on partitioning. I was wondering about switching to using a trigger to generate the sequence but maybe I'll just wait for the fix.
>
> 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.
The good thing is that I know the pattern of usage in this app pretty well, and the selects hugely outnumber the inserts (and are always going to be where performance is most required). So anything I can do which actually improves select performance is good, even if it means doubling the insert time.
>
> 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.
Thanks for the tip. It probably isn't useful in this case, though, because there is no difference in specificity between the two columns (they both contain exactly the same kind of data, from the same range of values).
Edited 1 time(s). Last edit at 05/24/2006 11:54AM by Jan Hansen.