MySQL Forums
Forum List  »  Performance

Re: Poor performance when using a large Limit offset
Posted by: James Day
Date: March 30, 2005 12:41AM

There is no inherent problem from using covering indexes. As usual, it's up to you to decide if the extra space is justified by the extra speed for the queries the index can be used for. Nothing wrong with having much more space for indexes than data if that does the job. End users are unlikely to notice the space use (though you might if you're buying expensive disk drives!) but are much more likely to notice any speed improvement.

In one application I've asked for a covering index which will approximately double the space needed for the data. it's worth doing because it changes a key query from one seek per record to roughly one seek per records/(records per page) - a massive improvement. The most common query is already being improved by arranging a better primary key/clustered index which puts adjacent records in the right order in the index nodes. But you can't get two completely diffeent physical orderings, so the covering index is the next best option.

If the query is a problem, I'd try it. Easy enough to remove the index later if you do decide that it isn't delivering enough benefit.

Options: ReplyQuote

Written By
Re: Poor performance when using a large Limit offset
March 30, 2005 12:41AM

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.