Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
None
-
None
-
None
Description
There is a comment in one of MySQL tests for InnoDB fulltext search:
Test that filesort is not used if ordering on same match expression as where clause
and the following test checks that:
CREATE TABLE wp( |
FTS_DOC_ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, |
title VARCHAR(255) NOT NULL DEFAULT '', |
text MEDIUMTEXT NOT NULL, |
dummy INTEGER, |
PRIMARY KEY (FTS_DOC_ID), |
UNIQUE KEY FTS_DOC_ID_INDEX (FTS_DOC_ID), |
FULLTEXT KEY idx (title,text) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
|
|
INSERT INTO wp (title, text) VALUES |
('MySQL Tutorial','DBMS stands for MySQL DataBase ...'), |
('How To Use MySQL Well','After you went through a ...'), |
('Optimizing MySQL','In this tutorial we will show ...'), |
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), |
('MySQL vs. YourSQL','In the following database to database comparison ...'), |
('MySQL Security','When configured properly, MySQL ...'); |
|
|
FLUSH STATUS;
|
|
|
SELECT title, MATCH(title, text) AGAINST ('database') AS score |
FROM wp |
WHERE MATCH(title, text) AGAINST ('database') |
ORDER BY score DESC; |
|
|
SHOW SESSION STATUS LIKE 'Sort%'; |
MySQL 5.6.10 (and 5.6.12) returns all zeros, as expected:
SHOW SESSION STATUS LIKE 'Sort%';
|
Variable_name Value
|
Sort_merge_passes 0
|
Sort_range 0
|
Sort_rows 0
|
Sort_scan 0
|
but MariaDB doesn't:
SHOW SESSION STATUS LIKE 'Sort%';
|
Variable_name Value
|
Sort_merge_passes 0
|
Sort_range 0
|
Sort_rows 2
|
Sort_scan 1
|
EXPLAIN from MySQL:
+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+
|
| 1 | SIMPLE | wp | fulltext | idx | idx | 0 | NULL | 1 | 100.00 | Using where; Using filesort |
|
+----+-------------+-------+----------+---------------+------+---------+------+------+----------+-----------------------------+
|
1 row in set, 1 warning (0.16 sec)
|
|
|
MySQL [test]> SHOW WARNINGS;
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select `test`.`wp`.`title` AS `title`,(match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) AS `score` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) order by `score` desc |
|
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
EXPLAIN from MariaDB:
+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+
|
| 1 | SIMPLE | wp | fulltext | idx | idx | 0 | | 1 | 100.00 | Using where; Using temporary; Using filesort |
|
+------+-------------+-------+----------+---------------+------+---------+------+------+----------+----------------------------------------------+
|
1 row in set, 1 warning (0.01 sec)
|
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select `test`.`wp`.`title` AS `title`,(match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) AS `score` from `test`.`wp` where (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) order by (match `test`.`wp`.`title`,`test`.`wp`.`text` against ('database')) desc |
|
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
bzr version-info
revision-id: sergii@pisem.net-20130717165112-i9klgxk4enpvc09a
|
revno: 3637
|
branch-nick: 10.0-monty
|
Attachments
Issue Links
- relates to
-
MDEV-4744 InnoDB Fulltext indexes
-
- Closed
-
Activity
The optimizations are rejected by you (they are only innodb related artificially), so it is left to remind. It could be closed but then some other should be opened.
It is just absence of new fts optimization. I already ported it. This particular test passes in my tree (some other optimization tests are failing)