2

l have a very large table in mysql database, and it is the first time to me to query a table so large like this. PS: about 2 million row, 40G size.

this is a table for news, so l have a fulltext index on field content, but l also need to order the result by date and other condition.

l tried to build normal index on field date but it failed. l googled it, cannot use normal index and fulltext index at the same time.

so assume l have a table like this

mysql> select * from test;
+----+--------+------+------------+------------------+
| id | title  | tag  | date       | content          |
+----+--------+------+------------+------------------+
| 38 | title1 | a    | 1514521364 | looooong content |
| 39 | title2 | a    | 1514521364 | looooong content |
| 40 | title3 | b    | 1514521364 | looooong content |
| 41 | title4 | c    | 1514521364 | looooong content |
| 42 | title5 | NULL | 1514521364 | looooong content |
+----+--------+------+------------+------------------+
5 rows in set (0.00 sec)

Now it has the fulltext index on content, the normal index on title, tag and date. How could l do it. thx so much. This problem has been bothering me for serval days.

Shadow
  • 33,525
  • 10
  • 51
  • 64
afraid.jpg
  • 965
  • 2
  • 14
  • 31
  • You already got your answer from googling: you cannot combine FT and non-FT indexes. You could try to use subqueries to make MySQL use different indexes, but that will probably take longer than a single pass without indexes. – Shadow Sep 03 '18 at 10:17
  • @Shadow ok, l will try subquery. it is too slow now. Average 5s only fulltext without order by, 300s with order by – afraid.jpg Sep 03 '18 at 10:24
  • @afraid.jpg Please edit your question to include the table definition (`CREATE TABLE`) of your table, including all other related informations like indices. Then include the query you run or want to run, as well with an `EXPLAIN` query of your `SELECT` statement. Include any error message you might get. – Progman Sep 03 '18 at 13:04

0 Answers0