I’ve a table described below, with two columns – integer primary key and title text – currently holding circa 3 million records. As seen in the metadata below, there’s a BTREE index on integer primary key column, and FULLTEXT index on title column.
MariaDB (ttsdata)> describe records;
+------------------+---------------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------------------+-------------------------------+
| id | int(15) unsigned | NO | PRI | NULL | auto_increment |
| title | varchar(2000) | YES | MUL | | |
+------------------+---------------------+------+-----+---------------------+-------------------------------+
MariaDB (ttsada)> show index from records;
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| records | 0 | PRIMARY | 1 | id | A | 2798873 | NULL | NULL | | BTREE | | |
| records | 1 | title | 1 | title | NULL | 1 | NULL | NULL | YES | FULLTEXT | | |
+---------+------------+-------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
I’d like to run the following query:
SELECT SQL_NO_CACHE * FROM records
WHERE
id > 1928177 AND
MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE)
LIMIT 200
This query takes more 5 seconds to execute. When I remove the the range part or the fulltext part, in both cases the query executes in circa 100 ms. Below is analysis of individual queries, the last one being the one I want to use.
I’m new to MySQL and DBA in general. I’ve posted EXPLAIN statements but I have no idea how to draw any conclusions from them. I assume that the query is slow because the range filtering happens on data set obtained from full text query.
So my question is: How can I make the query fast?
The 1928177 magic number is something that just happens to be needed.
Query 1
SELECT SQL_NO_CACHE * FROM records
WHERE id > 1928177
LIMIT 200
MariaDB (ttsdata)> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200;
+------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+
| 1 | SIMPLE | records | range | PRIMARY | PRIMARY | 4 | NULL | 227183 | Using index condition |
+------+-------------+---------+-------+---------------+---------+---------+------+--------+-----------------------+
1 row in set (0.005 sec)
MariaDB (ttsdata)> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 LIMIT 200;
...
200 rows in set (0.108 sec)
Time: 0.108 sec
Query 2
SELECT SQL_NO_CACHE * FROM records
WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE)
LIMIT 200
MariaDB (ttsdata)> explain SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200;
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | records | fulltext | title | title | 0 | | 1 | Using where |
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
1 row in set (0.007 sec)
MariaDB (ttsdata)> SELECT SQL_NO_CACHE * FROM records WHERE MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200;
...
200 rows in set (0.138 sec)
Time: 0.138 sec
Query 3
SELECT SQL_NO_CACHE * FROM records
WHERE
id > 1928177 AND
MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE)
LIMIT 200
MariaDB (ttsdata)> explain SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200;
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
| 1 | SIMPLE | records | fulltext | PRIMARY,title | title | 0 | | 1 | Using where |
+------+-------------+---------+----------+---------------+-------+---------+------+------+-------------+
1 row in set (0.005 sec)
MariaDB (ttsdata)> SELECT SQL_NO_CACHE * FROM records WHERE id > 1928177 AND MATCH (title) AGAINST ('+flower' IN BOOLEAN MODE) LIMIT 200;
...
200 rows in set (5.627 sec)
Time: 5.627 sec