MariaDB (MySQL) slow query when primary key range combined with fulltext index

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

Can’t understand difference in fulltext results – contains, contains with wildcard, freetext

I have a table with an fulltext index on the column named Filecontent. The table has a row where content contains "W 917". For context, the content column on this rows contains much more than just what I’m searching for.

I don’t understand why I’m getting different results depending on whether I’m using contains, contains with wildcard or freetext. Why is CONTAINS without wildcard getting results, but CONTAINS with wildcard doesn’t?

-- Searching for "W 917" -- No match - CONTAINS with wildcard SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917*"')  -- Match - CONTAINS SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W 917"')  -- Match - FREETEXT SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W 917"')   -- Searching for "W" -- Match - CONTAINS with wildcard SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W*"')  -- No match - CONTAINS SELECT * FROM InvoicePDFContent t1 WHERE CONTAINS(t1.Filecontent, '"W"')  -- No match - FREETEXT SELECT * FROM InvoicePDFContent t1 WHERE FREETEXT(t1.Filecontent, '"W"') ´´´ 

SQL ERROR [ mysqli ] Can’t find FULLTEXT index matching the column list [1191]

I faced this problem after importing the DB, recently i had to setup the fresh one so i used the already taken backup of files & db after importing db this following error shows,

General Error SQL ERROR [ mysqli ]

Can’t find FULLTEXT index matching the column list [1191]

SQL

SELECT  f.forum_id, f.forum_name, t.*,         MATCH (t.topic_title) AGAINST ('some topic') AS score,         fp.post_text AS first_post_text, lp.post_text AS last_post_text,         fpu.user_avatar AS fp_avatar,         fpu.user_avatar_type AS fp_avatar_type,         fpu.user_avatar_width AS fp_avatar_width,         fpu.user_avatar_height AS fp_avatar_height,         lpu.user_avatar AS lp_avatar,         lpu.user_avatar_type AS lp_avatar_type,         lpu.user_avatar_width AS lp_avatar_width,         lpu.user_avatar_height AS lp_avatar_height     FROM  (phpbb_topics t)     LEFT JOIN  phpbb_forums f  ON (f.forum_id = t.forum_id)     LEFT JOIN  phpbb_posts fp  ON (fp.post_id = t.topic_first_post_id)     LEFT JOIN  phpbb_users fpu  ON (fpu.user_id = t.topic_poster)     LEFT JOIN  phpbb_posts lp  ON (lp.post_id = t.topic_last_post_id)     LEFT JOIN  phpbb_users lpu  ON (lpu.user_id = t.topic_last_poster_id)     WHERE  MATCH (t.topic_title) AGAINST ('some topic') >= 0.1       AND  t.topic_status <> 2       AND  t.topic_visibility = 1       AND  t.topic_time > (UNIX_TIMESTAMP() - 315360000)       AND  t.topic_id <> 8       AND  f.similar_topics_ignore = 0     LIMIT  5  

BACKTRACE

FILE: (not given by php) LINE: (not given by php) CALL: msg_handler()

FILE: [ROOT]/phpbb/db/driver/driver.php LINE: 994 CALL: trigger_error()

FILE: [ROOT]/phpbb/db/driver/mysqli.php LINE: 193 CALL: phpbb\db\driver\driver->sql_error()

FILE: [ROOT]/phpbb/db/driver/mysql_base.php LINE: 45 CALL: phpbb\db\driver\mysqli->sql_query()

FILE: [ROOT]/phpbb/db/driver/driver.php LINE: 270 CALL: phpbb\db\driver\mysql_base->_sql_query_limit()

FILE: [ROOT]/phpbb/db/driver/factory.php LINE: 321 CALL: phpbb\db\driver\driver->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/core/similar_topics.php LINE: 210 CALL: phpbb\db\driver\factory->sql_query_limit()

FILE: [ROOT]/ext/vse/similartopics/event/listener.php LINE: 63 CALL: vse\similartopics\core\similar_topics->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php LINE: 184 CALL: vse\similartopics\event\listener->display_similar_topics()

FILE: [ROOT]/vendor/symfony/event-dispatcher/EventDispatcher.php LINE: 46 CALL: Symfony\Component\EventDispatcher\EventDispatcher->doDispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php LINE: 62 CALL: Symfony\Component\EventDispatcher\EventDispatcher->dispatch()

FILE: [ROOT]/phpbb/event/dispatcher.php LINE: 46 CALL: phpbb\event\dispatcher->dispatch()

FILE: [ROOT]/viewtopic.php LINE: 2370 CALL: phpbb\event\dispatcher->trigger_event()

Fulltext fields search — ONLY one keyword return none results

I am building search enhancement in Drupal 8 site. I have one search server: Acquia search API In the search API fields tab, i set up title, rendered HTML and taxonomy term as fulltext fields and boost them accordingly.

In my search view, i selected three of them as search fields and put the min keywords length as 1. view picture If i search two keywords, it works good like ‘ONE TWO’. If i only select ‘ONE’, it return none result.

The SQL query looks like the following: Query

Any suggestions will be appreciated.

Thanks.

MySQL – Boolean Full-text Search

I’m using MySQL’s boolean full-text search for my search engine: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html.

Assume I have a table with the following columns:

PRODUCTS -------- id (PK) color_id (FK to COLORS table) name description 

When given a search term, I want to search the product’s table’s name and description columns, as well as the product’s color name. The color’s name is in a separate table.

Should I create a new column in the product’s table and put all the text I want indexed in there? For example, I add a products.full_text_index column, and dump a concatenation of name, description, and the color’s name in that column? Then do full-text search against that column?

UPDATE: Or, instead of a column in an existing table, maybe create a new table to contain all the text that should be used for the full-text search. That table can just have a FK to the products table.