Re: [bug?] Re: Adding an index caused problems!

On 23.05.2019 21:46, J.O. Aho wrote:
> On 23/05/2019 20.56, Axel Schwenke wrote:

>> This query has to look at all rows, or at least to all tuples (state,
>> dollars) from the table. An optimal plan would require an index on (state,
>> dollars) to read the rows in GROUP BY order (hence no sorting required) from
>> the index only (which is called a /covering index read/)

> The OP may gain if he created a joint index for state and dollars
> ex: CREATE INDEX inx_state_dollars ON people (state,dollars);
> He should in that case drop the index for state and the one for dollars.

Not exactly. An index on (state, dollars) could be used in places where an index on (state) alone is needed. It could however not be use in stead of an index on (dollars). This is a general property of MySQL/MariaDB index usage that they can use a prefix of a composite index just as such an index itself.

Here is a nice write-up about this:
https://www.vividcortex.com/blog/the-left-prefix-index-rule

> For me with 262000+ rows, I drop from 1.23 sec (no index) to 0.24 sec with
> the joint index.

Jep. That's the difference between scanning the whole table and scanning the index only. If you would look up the sizes of both objects (i.e. in INFORMATION_SCHEMA) then the response time would correlate with the size.

That is 100% the expected behavior. Received on Thu May 23 2019 - 22:55:30 CEST