roberto spadim added a comment - 2014-12-02 00:09 i didn't tested... item_id/item_id_red columns are part of primary key, if you remove primary key and leave item and rendimento index, optimizer prefer item index?

Elena Stepanova added a comment - 2014-12-02 14:26 psergey , As requested, results (no need to go through previous comments, they are intermediate steps). The problem is that the query uses ref access with the index `item` on the table `a`, while range with index `rendimento` provides significantly better performance for this query. It is reproducible. Depending on the query, performance difference varied from 1.5x to 30x and more (in some cases I didn't wait for the "bad" query to finish). The query is simplified (see below) to leave only one join and only AND conditions. No subqueries. Also, all select items have been replaced by a single COUNT to make the result set nicer. The table structures are simplified to only have columns and indexes that directly participate in the query. Also, tables are switched from Aria to MyISAM, and partitioning is removed. The data for the second table is reduced to 2 rows. The data for the first table (the one with indexes in question) is shortened a bit, to 20 mln rows (the original data was 29 mln rows). It's also possible to reduce it to some 15 mln and still have the difference in plans, but the performance difference becomes negligible At this point, the performance difference on my machine is ~3x (0.13 vs 0.35 sec). On another machine ~4x (0.07 vs 0.30 sec). This is for "warm" queries, cold ones can take considerably longer and are less predictable. Below the "BAD" query is the one where the wrong index/access is used. The "GOOD" query is where the desired index is enforced. MariaDB [test]> select count (*) from t1_min; + ----------+ | count (*) | + ----------+ | 20000000 | + ----------+ 1 row in set (0.00 sec)   MariaDB [test]> select count (*) from t2_min; + ----------+ | count (*) | + ----------+ | 2 | + ----------+ 1 row in set (0.00 sec) CREATE TABLE `t1_min` ( `item_id` mediumint(8) unsigned NOT NULL DEFAULT '0' , `item_id_red` mediumint(9) NOT NULL DEFAULT '0' , `lote_rendimento_data` date NOT NULL DEFAULT '0000-00-00' , `rendimento` enum( 'Y' , 'N' ) NOT NULL DEFAULT 'N' , KEY `item` (`item_id`,`item_id_red`), KEY `rendimento` (`rendimento`,`lote_rendimento_data`,`item_id_red`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;   CREATE TABLE `t2_min` ( `plano_conta_id_red` bigint (20) NOT NULL DEFAULT '0' ) ENGINE=MyISAM DEFAULT CHARSET=latin1; MariaDB [test]> show index in t1_min; + --------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | + --------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t1_min | 1 | item | 1 | item_id | A | 5 | NULL | NULL | | BTREE | | | | t1_min | 1 | item | 2 | item_id_red | A | 4672 | NULL | NULL | | BTREE | | | | t1_min | 1 | rendimento | 1 | rendimento | A | 2 | NULL | NULL | | BTREE | | | | t1_min | 1 | rendimento | 2 | lote_rendimento_data | A | 3518 | NULL | NULL | | BTREE | | | | t1_min | 1 | rendimento | 3 | item_id_red | A | 338983 | NULL | NULL | | BTREE | | | + --------+------------+------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ SELECT COUNT (*) AS GOOD FROM t1_min a force index (rendimento), t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + ------+ | GOOD | + ------+ | 863 | + ------+ 1 row in set (0.13 sec)   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+-------+---------------+------------+---------+------+-------+----------+------------------------------------------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | a | range | rendimento | rendimento | 4 | NULL | 52529 | 75.00 | Using index condition; Using where ; Using join buffer (flat, BNL join ) |   | Note | 1003 | select count (0) AS `GOOD` from `test`.`t1_min` `a` FORCE INDEX (`rendimento`) join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y' ) and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13' ) and (`test`.`a`.`lote_rendimento_data` < '2014-10-17' )) | SELECT COUNT (*) AS BAD FROM t1_min a, t2_min AS b WHERE a.item_id=67 AND b.plano_conta_id_red=a.item_id_red AND a.lote_rendimento_data>= "2014-10-13" AND a.lote_rendimento_data< "2014-10-17" AND a.rendimento= 'Y' ; + -----+ | BAD | + -----+ | 863 | + -----+ 1 row in set (0.35 sec)   | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + ------+-------------+-------+------+-----------------+------+---------+---------------------------------+------+----------+------------------------------------+ | 1 | SIMPLE | b | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | | | 1 | SIMPLE | a | ref | item,rendimento | item | 6 | const,test.b.plano_conta_id_red | 4280 | 100.00 | Using index condition; Using where |   | Note | 1003 | select count (0) AS `BAD` from `test`.`t1_min` `a` join `test`.`t2_min` `b` where ((`test`.`a`.`item_id` = 67) and (`test`.`a`.`rendimento` = 'Y' ) and (`test`.`b`.`plano_conta_id_red` = `test`.`a`.`item_id_red`) and (`test`.`a`.`lote_rendimento_data` >= '2014-10-13' ) and (`test`.`a`.`lote_rendimento_data` < '2014-10-17' )) | I've uploaded the new dump to ftp.askmonty.org/private/mdev7125-smaller.dump.gz.

roberto spadim added a comment - 2015-02-01 16:37 hi guys any news?

Sergei Golubchik added a comment - 2022-09-12 10:15 10.0 was EOLed in March 2019