Fulltext search very slow with ORDER BY on other column on MySQL DB

Question

I'm stuck with a query on a InnoDB table in a MySQL database. I need to find orders based on a fulltext search on two text fields which contain order and customer details in json encoded text. Here is the table schema:

+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id      | int(11)    | NO   | MUL | NULL    |                |
| comment      | text       | NO   |     | NULL    |                |
| modified     | datetime   | NO   |     | NULL    |                |
| created      | datetime   | NO   | MUL | NULL    |                |
| items        | mediumtext | NO   | MUL | NULL    |                |
| addressinfo  | text       | NO   |     | NULL    |                |
+--------------+------------+------+-----+---------+----------------+
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders |          0 | PRIMARY  |            1 | id          | A         |       69144 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | user_id  |            1 | user_id     | A         |       45060 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | created  |            1 | created     | A         |       69240 |     NULL | NULL   |      | BTREE      |         |               |
| orders |          1 | search   |            1 | items       | NULL      |       69240 |     NULL | NULL   |      | FULLTEXT   |         |               |
| orders |          1 | search   |            2 | addressinfo | NULL      |       69240 |     NULL | NULL   |      | FULLTEXT   |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

The table has around 150.000 rows. It has one fulltext index on the items and addressinfo column.

And here comes the query:

SELECT 
  id
FROM 
  orders 
WHERE 
  MATCH (items, addressinfo) AGAINST (
    '+simon* +white* ' IN BOOLEAN MODE
  ) 
ORDER BY 
  id DESC 
LIMIT 
20

This is the EXPLAIN result:

+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
| id | select_type | table  | partitions | type     | possible_keys | key    | key_len | ref   | rows | filtered | Extra                                             |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | orders | NULL       | fulltext | search        | search | 0       | const |    1 |   100.00 | Using where; Ft_hints: no_ranking; Using filesort |
+----+-------------+--------+------------+----------+---------------+--------+---------+-------+------+----------+---------------------------------------------------+

On large resultsets the query takes around 30 seconds to process on a standard LAMP VM.

Without ordering by

ORDER BY id DESC

the query is processed much faster in around 0.6 seconds.

The only difference in the EXPLAIN result is that "Using filesort" is missing in the faster query. Measuring the query says that 98% of the processing time (27s) is used for "Creating Sort Index".

Is there any way to do the fulltext search on this table with ORDER BY in a reasonable processing time (less than a second)?

I already tried different approaches e.g. putting the order by column into the fulltext index (text_id as TEXT column) with no luck. The approach from here: How to make a FULLTEXT search with ORDER BY fast? is also not faster.

As the application runs on a shared host I'm very limited in optimizing MySQL ini values or Memory values.

Thanks a lot!

Answer 1

You might gain some time when using a delivered table. try it.

Query

SELECT
 orders.id
FROM (
  SELECT 
    id  
  FROM 
    orders 
  WHERE 
    MATCH (items, addressinfo) AGAINST (
      '+simon* +white* ' IN BOOLEAN MODE
    )  
) 
  AS 
    orders_match
INNER JOIN
 orders 
ON
 orders_match.id = orders.id

ORDER BY
 orders.id DESC

LIMIT 20