|
Hi,
Could you please provide SHOW CREATE TABLE results for `agentes` and `otra` tables?
Also,
EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC
|
could be useful.
Thanks.
|
|
MariaDB [test]> show create table agentes;
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
| agentes | CREATE TABLE `agentes` (
|
`id` int(11) NOT NULL,
|
`nombre` varchar(255) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> show create table otra;
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| otra | CREATE TABLE `otra` (
|
`id_algo` int(11) NOT NULL,
|
`id_agente` int(11) NOT NULL,
|
PRIMARY KEY (`id_algo`,`id_agente`),
|
KEY `another_data` (`id_agente`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
+------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
|
| 1 | PRIMARY | otra | index | another_data | PRIMARY | 8 | NULL | 6 | 100.00 | Using index; Using temporary; Using filesort; LooseScan |
|
| 1 | PRIMARY | agentes | eq_ref | PRIMARY | PRIMARY | 4 | test.otra.id_agente | 1 | 100.00 | |
|
+------+-------------+---------+--------+---------------+---------+---------+---------------------+------+----------+---------------------------------------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
|
|
|
Hi Jose,
Thank you.
As a workaround, please try to set optimizer_switch='semijoin=off' or optimizer_switch='loosescan=off'
|
|
Neither helps.
MariaDB [test]> set optimizer_switch='semijoin=off';
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> set optimizer_switch='loosescan=off';
|
Query OK, 0 rows affected (0.00 sec)
|
|
MariaDB [test]> explain extended SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
+------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|
| 1 | PRIMARY | agentes | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where; Using filesort |
|
| 2 | MATERIALIZED | otra | index | another_data | PRIMARY | 8 | NULL | 6 | 100.00 | Using index |
|
+------+--------------+---------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
|
2 rows in set, 1 warning (0.00 sec)
|
|
MariaDB [test]> SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
+----+--------+
|
| id | nombre |
|
+----+--------+
|
| 1 | row 1 |
|
| 1 | row 1 |
|
| 1 | row 1 |
|
| 2 | row 2 |
|
| 2 | row 2 |
|
| 3 | row 3 |
|
+----+--------+
|
6 rows in set (0.00 sec)
|
|
|
Hi Jose,
I suppose you have query_cache enabled. If so, after you had set semijoin=off or loosescan=off, either run the query as SELECT SQL_NO_CACHE * FROM ..., or execute FLUSH TABLES, or disable the query cache (set global query_cache_size=0).
|
|
Test case (exactly the same as attached + query from the description, just all together in the form suitable for MTR):
--source include/have_innodb.inc
|
|
CREATE TABLE IF NOT EXISTS `agentes` (
|
`id` int(11) NOT NULL,
|
`nombre` varchar(255) NOT NULL,
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO `agentes` (`id`, `nombre`) VALUES
|
(1, 'row 1'),(2, 'row 2'),(3, 'row 3'),
|
(4, 'row 4'),(5, 'row 5'),(6, 'row 6');
|
|
CREATE TABLE IF NOT EXISTS `otra` (
|
`id_algo` int(11) NOT NULL,
|
`id_agente` int(11) NOT NULL,
|
PRIMARY KEY (`id_algo`,`id_agente`),
|
KEY `another_data` (`id_agente`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO `otra` (`id_algo`, `id_agente`) VALUES
|
(1, 1),(1, 2),(2, 1),(2, 2),(2, 3),(3, 1);
|
|
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
|
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);
|
Actual result:
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
id nombre
|
1 row 1
|
1 row 1
|
1 row 1
|
2 row 2
|
2 row 2
|
3 row 3
|
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);
|
id nombre
|
1 row 1
|
2 row 2
|
3 row 3
|
Expected result:
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
id nombre
|
1 row 1
|
2 row 2
|
3 row 3
|
SELECT * FROM agentes WHERE id in (select distinct id_agente from otra);
|
id nombre
|
1 row 1
|
2 row 2
|
3 row 3
|
|
|
The problem seems to be semijoin, with it disabled and SQL_NO_CACHE It got the right result.
Thanks.
|
|
Query plan without ORDER BY:
MariaDB [j21]> explain select * FROM agentes WHERE id in (select distinct id_agente from otra);
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|
| 1 | PRIMARY | agentes | ALL | PRIMARY | NULL | NULL | NULL | 6 | |
|
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
|
| 2 | MATERIALIZED | otra | index | another_data | PRIMARY | 8 | NULL | 6 | Using index |
|
+------+--------------+-------------+--------+---------------+--------------+---------+------+------+-------------+
|
This seems ok.
Query plan with ORDER BY:
MariaDB [j21]> explain SELECT * FROM agentes WHERE id in (select distinct id_agente from otra) ORDER BY nombre ASC;
|
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+
|
| 1 | PRIMARY | otra | index | another_data | PRIMARY | 8 | NULL | 6 | Using index; Using temporary; Using filesort; LooseScan |
|
| 1 | PRIMARY | agentes | eq_ref | PRIMARY | PRIMARY | 4 | j21.otra.id_agente | 1 | |
|
+------+-------------+---------+--------+---------------+---------+---------+--------------------+------+---------------------------------------------------------+
|
This seems to be wrong. otra.PRIMARY is defined as
PRIMARY KEY (`id_algo`,`id_agente`),
|
We need to produce a distinct stream of otra.id_agente. It is not possible to do with LooseScan - there are different values of id_algo, and index scan will produce data ordered by id_algo, id_agente.
Somehow, the presence of ORDER BY causes invalid query plan to be chosen. Will investigate further.
|
prueba.sql