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

From: Luuk <luuk_at_invalid.lan>
Date: Thu, 23 May 2019 19:50:27 +0200
Message-ID: <5ce6dd64$0$22354$e4fe514c_at_news.xs4all.nl>

[Quoted] On 23-5-2019 10:23, Axel Schwenke wrote:
> On 22.05.2019 22:38, Richard Ryan wrote:

>> Found it interesting that adding something as seemingly innocuous as an index can cripple a query that ran previously in a reasonable amount of time.

>
> On the contrary. The database chooses an execution plan for a query based on
> the available indexes. Different indexes will (quite likely) give different
> plans. You might want to learn about EXPLAIN.
>
> 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.
>

[Quoted] 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;

+----------------+---------+

| left(state,10) | dollars |

+----------------+---------+
| AAAAAAAAAA     |  100.00 |
| AAAAAAAAAA     |  100.00 |
| AAAAAAAAAA     |  106.00 |
| AAAAAAAAAA     |  106.00 |
| AAAAAAAAAA     |  127.00 |
| AAAAAAAAAA     |  127.00 |
| AAAAAAAAAA     |  143.00 |
| AAAAAAAAAA     |  143.00 |
| AAAAAAAAAA     |  119.00 |
| AAAAAAAAAA     |  119.00 |
+----------------+---------+

10 rows in set (0.02 sec)

MariaDB [test]> explain select left(state,10), (dollars) from people where dollars<150 order by state limit 10\G

*************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: people
          type: range
possible_keys: inx_dollars
           key: inx_dollars
       key_len: 6
           ref: NULL
          rows: 3473
         Extra: Using index condition; Using filesort

1 row in set (0.00 sec)

MariaDB [test]> select _at__at_VERSION;

+-----------------+
| _at__at_VERSION       |
+-----------------+

| 10.2.22-MariaDB |

+-----------------+

1 row in set (0.01 sec)

Its 'claiming' to use key on inx_dollars but using inx_state

MariaDB [test]> show create table people\G

*************************** 1. row ***************************
        Table: people

Create Table: CREATE TABLE `people` (

   `id` int(11) DEFAULT NULL,
   `last_name` varchar(255) DEFAULT NULL,
   `first_name` varchar(255) DEFAULT NULL,
   `address` varchar(255) DEFAULT NULL,
   `city` varchar(255) DEFAULT NULL,
   `state` varchar(255) DEFAULT NULL,
   `zip` char(10) DEFAULT NULL,
   `join_date` date DEFAULT NULL,
   `last_sale_date` date DEFAULT NULL,
   `dollars` decimal(10,2) DEFAULT NULL,
   KEY `inx_join_date` (`join_date`),

   KEY `inx_dollars` (`dollars`),
   KEY `inx_state` (`state`(50))
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

-- 
Luuk

Received on Thu May 23 2019 - 19:50:27 CEST