Re: [bug?] Re: Adding an index caused problems!
Date: Thu, 23 May 2019 20:56:02 +0200
Message-ID: <qc6qc2$4er$1_at_dont-email.me>
On 23.05.2019 19:50, Luuk wrote:
> On 23-5-2019 10:23, Axel Schwenke wrote:
>> There is however something broken here. Since your query has to read *all* >> rows in the table, no index should ever be used. Either you have enabled >> some strange optimizer switch or you discovered a bug. >> >>> Version: MySQL 7.7.24 >> >> This is impossible. No such version ever existed. >>
>
> He probably means 5.7.24
>
>
> About the index/optimezer/bug ?
>
> MariaDB [test]> select left(state,10), (dollars) from people where
> dollars<150 order by state limit 10;
[Quoted] Now that is a completely different query. Honestly I just looked at the opening post of the thread and there the query is:
SELECT
p.state, avg(p.dollars) FROM people p GROUP BY p.state
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/)
An index on (state) alone could be used to read rows in GROUP BY order, but then it had to read the `dollars` field from the row, accessing all (!) rows in index order; which would be random storage order. And this would almost certainly be slower than reading rows in table order (= storage order) and doing the summing in a temporary table.
Exactly for that reason there used to be a limit of IIRC 30%. If a query has to read more rows than that percentage of the table, a table scan is preferred over an index access. Received on Thu May 23 2019 - 20:56:02 CEST