MySQL Forums
Forum List  »  Optimizer & Parser

Re: Compound index (pkey) not used when it should be
Posted by: Dan Parker
Date: November 29, 2017 04:38PM

Quote

InnoDB seems to have decided that using the data file will be just as fast.

I'll read that section, but it makes absolutely no sense at all to me how scanning most (or even much) a 9 million+ row table could possibly be as fast as using an index that exactly matches both your search criteria and the columns you're retrieving only 21 rows for...no matter how the buffer pool is configured (all settings are at their installation defaults currently).

Quote

What's the performance without the Limit clause?

Eliminating the LIMIT clause has no effect on the EXPLAIN results, except for the number for "rows" (9316207 instead of 21). Naturally the performance is far worse.

Quote

Did you try Force Index, and if so with what result?

Using FORCE INDEX (PRIMARY) has absolutely no effect on either the EXPLAIN results or the actual query performance.

I've tried many, many workarounds (changing character set to 'latin1', etc) based on similar reports I've found in other forums going back as far as 2010. None have had any impact at all. I can scarcely imagine a more simple case in which use of a compound key is obviously the correct...and really only rational...strategy for implementing a query, and where one really should not need to resort to tricking the optimizer into doing so.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Compound index (pkey) not used when it should be
208
November 29, 2017 04:38PM


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.