|
Update: After checking the result of EXPLAIN, it seems that this is related to the use of temporary tables  (please forgive my language, I am not very familiar with SQL)
The version where it does not work has "Using filesort" while the one that works has a "Using temporary; Using filesort"
After finding that the temporary tables could be due to ORDER BY on a same column, I made a workaround :
added a "ORDER BY table1.entityKey DESC" in the subquery.
This seems to resolve the issue. Perhaps it points to a solution.
The query that works is (in bold underlined the addition to make it work) is:
Select table1.entityKey
from table1
where table1.Deleted = 0
and table1.MasterKey is null
and table1.entityTypeKey = 8
and table1.entityKey in
(select table2.entityKey
from table2
where table2.Flag <> 2
and (table2.IndexKey = 4 and MATCH (table2.xhtmltext) AGAINST ('gold')) ORDER BY entityKey DESC)
order by table1.entityKey DESC
|
|
Thanks for the report. As a workaround, you can set optimizer_switch=orderby_uses_equalities=off in the config file, it should help.
|
Test case
|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (entityKey INT PRIMARY KEY) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1),(2),(3);
|
|
CREATE TABLE t2 (entityKey INT, xhtmltext TEXT, FULLTEXT(xhtmltext)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (1,"silver"),(2,"gold"),(3,"gold"),(4,"gold");
|
|
SELECT t1.entityKey FROM t1
|
WHERE t1.entityKey IN (
|
SELECT t2.entityKey FROM t2
|
WHERE MATCH (t2.xhtmltext) AGAINST ('gold')
|
)
|
ORDER BY t1.entityKey DESC;
|
|
# Cleanup
|
DROP TABLE t1, t2;
|
|
Actual result on 10.2 with default optimizer_switch
|
MariaDB [test]> SELECT t1.entityKey FROM t1
|
-> WHERE t1.entityKey IN (
|
-> SELECT t2.entityKey FROM t2
|
-> WHERE MATCH (t2.xhtmltext) AGAINST ('gold')
|
-> )
|
-> ORDER BY t1.entityKey DESC;
|
Empty set (0.06 sec)
|
|
Expected result
|
MariaDB [test]> SELECT t1.entityKey FROM t1
|
-> WHERE t1.entityKey IN (
|
-> SELECT t2.entityKey FROM t2
|
-> WHERE MATCH (t2.xhtmltext) AGAINST ('gold')
|
-> )
|
-> ORDER BY t1.entityKey DESC;
|
+-----------+
|
| entityKey |
|
+-----------+
|
| 3 |
|
| 2 |
|
+-----------+
|
2 rows in set (0.08 sec)
|
After fixing, please also check this test case. It differs from the first one only in the highlighted line, one extra value is inserted:
|
Another test case
|
--source include/have_innodb.inc
|
|
CREATE TABLE t1 (entityKey INT PRIMARY KEY) ENGINE=InnoDB;
|
INSERT INTO t1 VALUES (1),(2),(3),(4);
|
|
CREATE TABLE t2 (entityKey INT, xhtmltext TEXT, FULLTEXT(xhtmltext)) ENGINE=InnoDB;
|
INSERT INTO t2 VALUES (1,"silver"),(2,"gold"),(3,"gold"),(4,"gold");
|
|
SELECT t1.entityKey FROM t1
|
WHERE t1.entityKey IN (
|
SELECT t2.entityKey FROM t2
|
WHERE MATCH (t2.xhtmltext) AGAINST ('gold')
|
)
|
ORDER BY t1.entityKey DESC;
|
|
# Cleanup
|
DROP TABLE t1, t2;
|
|
Actual result from the 2nd test case
|
MariaDB [test]> SELECT t1.entityKey FROM t1
|
-> WHERE t1.entityKey IN (
|
-> SELECT t2.entityKey FROM t2
|
-> WHERE MATCH (t2.xhtmltext) AGAINST ('gold')
|
-> )
|
-> ORDER BY t1.entityKey DESC;
|
+-----------+
|
| entityKey |
|
+-----------+
|
| 4 |
|
| 4 |
|
| 4 |
|
+-----------+
|
3 rows in set (0.00 sec)
|
With optimizer_switch=orderby_uses_equalities=off both test cases return expected results.
|
maria_extra_settings.cfg.txt