|
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
|
|