I'm trying to track down some performance issues in my FULLTEXT MATCH() AGAINST() query, and it seems to be related (at least in part) to queries requiring an exact phrase. My table stores system process information, has roughly 2M rows, a keywords TEXT column, and a FULLTEXT index on said column. The keywords column averages ~600 characters, with several "words" in that column containing one or many special characters. The query I'm trying to run is an exact match on "net.exe": `select id from processes where id " > > A phrase that is enclosed within double quote (") characters matches > only rows that contain the phrase literally, as it was typed. The > full-text engine splits the phrase into words and performs a search in > the FULLTEXT index for the words. Nonword characters need not be > matched exactly: Phrase searching requires only that matches contain > exactly the same words as the phrase and in the same order. For > example, "test phrase" matches "test, phrase". ...and indeed, running queries with ... AGAINST('"net exe"' ... take just as long. So it seems to just be searching for exact phrases in general. My latest theory is that because my table has process info (e.g. system paths and cmdline arguments, which have many special characters), the normal FULLTEXT isn't useful for my query and MySQL is effectively re-indexing the whole table when I search for phrases like "net.exe". Some supporting evidence for this is that the original creation of the FULLTEXT index took roughly 30 minutes. However I find it hard to believe that would be the full explanation. Regardless, I ran explain on my query (which itself took 30 minutes to resolve), and got the following: ``` mysql> explain select id from processes where id explain select id from processes where id Ft_hints: sorted, which seems to only be due to the lack of quotes. If I run explain when querying for "net" it goes back to Ft_hints: no_ranking. Lastly, I tried running CHECK TABLE and even making a fresh temp table with only the id and keywords columns, but the above numbers were consistent, so I don't feel this is specific to my table condition.

I have an InnoDB full-text table that serves the Ajax-powered search box at the top of my website. I generate it with a daily script that pulls data from a dozen entity tables on the site and amalgamates them all into one FT table for searching. To give users the best experience (IMHO) I take whatever their input is, clean certain characters out of it (all full-text modifiers, for example), and then prepend every term with + and append them all with *. So a search for "stack overflow" becomes +stack* +overflow* The column that I'm searching on the FT table is small, with a typical character length of 30 characters. Event names, people's names, geographical locations, that sort of thing. Not huge passages of text. It works, but queries take on the order of 1 second to be returned. **EDIT: just after posting I've rebuilt the index and it's down to 0.4 seconds now - but I'd still like to improve it, if possible.** How could I change that to 0.1 seconds, or is that a pipe dream? My server is a dual Xeon with 16 cores/32 threads and 128GB of memory. I serve a million pages or so each month, and rarely see server load above 1-2, with plenty of spare memory. I wonder if I can somehow force this table to reside permanently in memory (rebuilding it after a server reboot or MySQL restart only takes 30 seconds or so), and if that would help? Or maybe MySQL is already holding it in memory - how can I check? I'm happy with the query itself, I don't think there's much that I can improve about it, but I know very little about how to maximize server potential through configuration. FWIW SELECT VERSION() gives me 10.3.20-MariaDB-log.

When attempting to start the SQL Full-text Filter Daemon Launcher service through the SQL Server Configuration Manager, it attempts to launch, but then fails. In the event log we get the following error message: "The SQL Full-text Filter Daemon Launcher (instancename) service failed to start due to the following error: Access is denied." It is currently set in "Manual" start mode. And uses the default service account generated at installation, "NT Service\MSSQLFDLauncher$instancename". Named pipes is enabled. This service was not installed as part of the original SQL Server installation, but done after. This is a SQL Server 2016 Enterprise (13.0.5216.0) instance.

I have a MongoDB collection db.articles,it has 2 keys ,"title" and "abstract".I want to do text search on these two keys.For example, the search text is "physics",and I want all the documents whose "title" or "abstract" contains keyword "physics" is returned.But how to create the text index to meet my command is really confusing me: Should I create two separate text indexes for both of them like this: db.articles.ensureIndex({title:"text"}) db.articles.ensureIndex({abstract:"text"}) or should I create a index in a single command and give the equal weight: db.articles.ensureIndex( { title: "text", abstract: "text", }, { weights: { title: 1, abstract:1, }, name: "TextIndex" } ) I am already get used to the operation find(),whose query granularity is key,that is ,you should indicate the key you want to query on.But for text index, it seems like a document granularity,you cannot indicate the key you want to query on , instead ,you can only indicate the document name.So , what can I do if I want to do text search on a special key?

I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part).
There is a postgres function where I SELECT concat_ws(' ', a."my_column1") INTO c1; SELECT concat_ws(' ', a."my_column2") INTO c2; RETURN setweight(to_tsvector('pg_catalog.simple', c1), 'C') || setweight(to_tsvector('pg_catalog.english', c2), 'D'); This is being written into a column called *fts_document* in the same table. I have INSERT and UPDATE triggers on this table and a trigger function as in the tutorial. Everything has worked fine at some point. After a dump from another db with identical structure and a restore to an empty db I got my table with a gap of the field id from 7306 to 21431. The main problem is that now the full-text-search retrieves documents only from this missing id range and the *my_column1* and *my_column2* values are very valid ones. I have deleted the index on *fts_document* column and rebuilt it, but no result. Where does postgres retrieve these weird ("not existing") rows from? How can I fix the search to retrieve also the existing rows and postgres to show the missing docs?

I have a database that was created many SQL Server versions ago that has Full Text files. It is currently on a SQL 2012 server. How can I confirm that there is no data in use in the Full Text index files so I can delete them?

On my Mac install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
 to_tsvector
-------------
 'abcd':1
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |   description   | token |  dictionaries  |  dictionary  | lexemes
-----------+-----------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols   |  สวัสดี | {}             |              |
(2 rows)

On my Linux install of PG:

=# select to_tsvector('english', 'abcd สวัสดี');
    to_tsvector
-------------------
 'abcd':1 'สวัสดี':2
(1 row)

=# select * from ts_debug('hello สวัสดี');
   alias   |    description    | token |  dictionaries  |  dictionary  | lexemes
-----------+-------------------+-------+----------------+--------------+---------
 asciiword | Word, all ASCII   | hello | {english_stem} | english_stem | {hello}
 blank     | Space symbols     |       | {}             |              |
 word      | Word, all letters | สวัสดี  | {english_stem} | english_stem | {สวัสดี}
(3 rows)

So something is clearly different about the way the tokenisation is defined in PG. My question is, how do I figure out what is different and how do I make my mac install of PG work like the Linux one? On both installs:

# SHOW default_text_search_config;
 default_text_search_config
----------------------------
 pg_catalog.english
(1 row)

# show lc_ctype;
  lc_ctype
-------------
 en_US.UTF-8
(1 row)

So somehow this mac install thinks that thai letters are spaces... how do I debug this and fix the "Space Symbol" definition here. Interestingly this install works with Armenian, but falls over when we reach Hebrew

=# select * from ts_debug('ԵԵԵ');
 alias |    description    | token |  dictionaries  |  dictionary  | lexemes
-------+-------------------+-------+----------------+--------------+---------
 word  | Word, all letters | ԵԵԵ   | {english_stem} | english_stem | {եեե}
(1 row)

=# select * from ts_debug('אאא');
 alias |  description  | token | dictionaries | dictionary | lexemes
-------+---------------+-------+--------------+------------+---------
 blank | Space symbols | אאא   | {}           |            |
(1 row)

Only significant diff I am seeing is that one is compiled with clang and one with gcc

PostgreSQL 11.2 on x86_64-apple-darwin18.2.0, compiled by Apple LLVM version 10.0.0 (clang-1000.11.45.5), 64-bit

VS 

 PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit

I am using the ruby pg_search gem, where code like this: Food.search("orange") produces a query like this. SELECT "foods".* FROM "foods" INNER JOIN (SELECT "foods"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("foods"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')), 0)) AS rank FROM "foods" WHERE ((to_tsvector('simple', coalesce("foods"."name"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'orange' || ' ''' || ':*')))) AS pg_search_d4392ced9dff0647fed4ed ON "foods"."id" = pg_search_d4392ced9dff0647fed4ed.pg_search_id ORDER BY pg_search_d4392ced9dff0647fed4ed.rank DESC, "foods"."id" ASC; Ive created this index, which seems to work quite well CREATE INDEX concurrently foods_gin ON foods USING gin (to_tsvector('simple', coalesce("foods"."name"::text, ''))); I have another column, category. there are 9 categories. For a given search as shown above for name, I want to show results from category 9 first, and then all the other categories (order doesn't matter). ideally the results could be paginated, so a perfect query/index combo would be great. is this possible to do, and/or will it be a truly gigantic index? p.s. suggestions for a better name for the question are welcome

Just checking I'm understanding this correctly: CREATE TABLE customer ( id INT NOT NULL AUTO_INCREMENT, name TINYTEXT NOT NULL, PRIMARY KEY (id), FULLTEXT (name) ) ENGINE = InnoDB; INSERT INTO customer VALUES (1, "ABC.DEF"); INSERT INTO customer VALUES (2, "ABC_DEF"); INSERT INTO customer VALUES (3, "ABC'DEF"); INSERT INTO customer VALUES (4, "ABC.DE"); INSERT INTO customer VALUES (5, "ABC.DFF"); Where I've got innodb_ft_min_token_size set to 3 (default is 4). --- When running: SELECT c.*, MATCH (name) AGAINST ("+ABC +DEF" IN BOOLEAN MODE) AS m FROM customer AS c Customers 1 and 3 match, because the . and ' are seen as word separators ([annoying for O'Brien](https://dba.stackexchange.com/q/250524)) . For customer 2, because the underscore gets the whole name treated as a single word, the "DEF" word cannot be found. --- If I change the MATCH to "+ABC +DE". 1, 2, or 3 do not match because this is using a full word match ("+DE" does not match "DEF"). 4 does not match because... innodb_ft_min_token_size is set to 3? As in, the 2 letter "DE" word is not in the FULLTEXT INDEX? --- If I change the MATCH to use asterisks (e.g. "+ABC* +DE*"), that will use *prefix* matching. But will only add customers 1 and 3 to the selection. Because the 2 letter "DE" word for customer 4 is not in the FULLTEXT INDEX? --- If I change the MATCH to use "+ABC.DE*", it matches all of them. Note how they all get the same rank (even customer 5), and this is no different to "+ABC*", where MATCH seems to be seeing the "DE*" as a separate word, and not matching it against anything. Whereas "+ABC DE*" is explicitly keeping it as a separate word, and the scores are handled appropriately. --- While the individual points make sense, I'm not sure this creates a good system. For a bit more consistency, I'm wondering if the database should ignore short words (tokens) in the MATCH query, in the same way it does when building the FULLTEXT INDEX. Only because I don't think "+DE" will ever do anything useful when the min token size is 3; and it's not exactly easy for the developer to identify what the individual words in the FULLTEXT INDEX will be (i.e. to remove them).

I am using PostgreSQL 10. I have a "booktbl" table which has 4 book_name paragraph_title content ts_content ts_content column is a tsvector column. When Iexecute this query: select book_name , paragraph_title ts_headline(content,to_tsquery('english','happy'), 'HighlightAll=true MaxFragments=100 FragmentDelimiter=$') from bookstbl where ts_content @@ (to_tsquery('happy')) ; I got a single row as a result which has a "ts_headline" column with all highlighted text search fragments. I want to convert all these fragments to individual rows. Like this: book para_title1 fragment1 book para_title2 fragment2 How can I achieve this in PostgreSQL full text search?

I'm developing an app that has a MySQL table, addresses, which stores events that happen at a given US address. I receive this data from a third-party in large chunks at set intervals. The table has an id primary key, an address as VARCHAR(255), an event_id VARCHAR column which relates back to the main events table, and a number of other columns which contain information about the event. For example: | id | address | event_id | characteristic_1 | characteristic_2 | characteristic_3 | |----|---------------------------------------------------------|----------|------------------|------------------|------------------| | 1 | 123 Main St, Nowhere, KS 66002 | 9001 | foo | bar | baz | | 2 | 1600 Pennsylvania Avenue NW, Washington, DC 20500 | 6B00 | blib | blob | bloo | | 3 | 124 Conch Street, Bikini Bottom, Marshall Islands 96970 | 374A | me | hoy | minoy | The table has 250 million rows with these types of addresses and grows each year. Data is never deleted. The table is read-only in production. The goal is to create a national autosuggest search roughly similar to one that you'd find on a large real estate portal website. I found that using a LIKE query with a B-Tree index on the address column is too slow in some cases, sometimes taking up to 20 seconds to run a basic search, so I'm now in the process of creating a FULLTEXT index which is probably going to take a day or two to finish. Before:

SELECT * FROM addresses WHERE address LIKE '%main st%' LIMIT 10;

What I imagine the query will look like after:

SELECT * FROM addresses WHERE MATCH(address) AGAINST('main st' IN NATURAL LANGUAGE MODE) LIMIT 10;

Due to the large table size (250 million rows and growing), is using a FULLTEXT index the best way to go, should I be doing something to optimize it based on my use case, or should I jump ship entirely and use another system like Meilisearch for autosuggest?

I need to store text in Quill-Delta format, which is in the form of a JSON array, e.g. | id | title | content | | --- | --- | --- | | 10 | Hello | [ { "insert": "Hello" }, { "insert": "World" } ]| | 20 | Image | [ { "insert": { "image": "data:image/j..." } } ]| Then, I need to query it, e.g. select where text contains "hello world" such as: 1. if I query content for "hello world" I get row 10; 2. if I query content for "insert" I get no results; 3. if I query content form "image" I get no results. In practice, it should search only the insert key of array objects, where the value is a JSON string (no other types). Can I achieve this storing data in Postgres? I read about full-text search over JSONB, but I couldn't come up with a working example.

Based on past answers, I see that it was previously possible to perform fulltext searches with double quotes and wildcard, like the following:

SELECT COUNT(*) FROM mytable MATCH (mycolumn) AGAINST ('"myemail@gma"*');

however, when I try this on the latest MySQL version(s), 8.0.25, I get an error:

ERROR 1064 (42000): syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'

Is there any way to make this type of search work? The only alternative I can see, in the email case, is to search the terms separated, with an AND condition (e.g. '+myemail +gma*', assuming stopwords disabled), but the semantics of the search are different.

I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for. I initially thought that I could do this with locate() to get the index of the string minus ~10 characters and then substring around 200 characters after but this only works if there is a document that includes these words consecutively. For example: SELECT id,created_at, modified_at, SUBSTRING(content, (locate('pto request', content))) as excerpt FROM search_index WHERE MATCH (name,content) AGAINST ('pto request' IN BOOLEAN MODE) The match clause finds documents that contains the words pto and request that may not be consecutive but the the locate function requires the exact string "pto request" to be somewhere in the document, if it isn't, an excerpt is not generated. My first thought was to check for 0, if 0 locate didn't match anything so remove a word and try again and keep doing so until a locate finds a match, but this could get really messy. Is there a better way?

I have a table table1 that contains several fields, some textual and other non-textual, on which I do search queries. Let's focus on some text fields, which I simply call field1, field2, field3. The table contains about 1.5 million records and the queries are not so fast, because when I have to search on these fields, I'm doing in fullscan mode. I give you a concrete example: SELECT * FROM table1 WHERE [some conditions] AND (field1 LIKE "%str1%" OR field2 LIKE "%str1%" or field3 LIKE "%str1%") AND (field1 LIKE "%str2%" OR field2 LIKE "%str2%" or field3 LIKE "%str2%") AND (field3 LIKE "%str3%") Point out that str1, str2, etc ... can be a word or a part of a word (for example "Table" or "Tabl"). Considering how the query is build, I don't see any sense to define any index on field1, field2, field3: they would not bring any advantage, just an useless waste of resources in my opionion. So, I have evaluated the FULLTEXT indexes of MySQL and the query MATCH() AGAINST(): this is not good, because in my case it works only when exactly **field1=word1** or **field2=word2**, while often the fields contain concatenated words without delimiters (for example field1 contains the value **"HOUSETABLERAIN"** and not **"HOUSE TABLE RAIN"**). What can I do to improve the execution time? My question includes changes to the query and the edit of data structure as well. I thought two solutions, but I'm not convinced of either. **Solution A)** Introduce another table table2 (table1_id INT, field VARCHAR, string VARCHAR); for each record in table1, insert all possible substrings (confining itself to combinations with at least 4 characters). Practical example: If in table1 there is the record id | field1 45 | HOUSETABLERAIN then in table2 there will be table1_id | field | string 45 | field1 | HOUS 45 | field1 | HOUSE 45 | field1 | HOUSET 45 | field1 | HOUSETA 45 | field1 | HOUSETAB 45 | field1 | HOUSETABL 45 | field1 | HOUSETABLE 45 | field1 | HOUSETABLER 45 | field1 | HOUSETABLERA 45 | field1 | HOUSETABLERAI 45 | field1 | HOUSETABLERAIN 45 | field1 | OUSE 45 | field1 | OUSET 45 | field1 | OUSETA ... Afterwards I could define an index on table2.string and I should have some advantages; furthermore, I could also eliminate a part of the fields in table1, because I would no longer search on them. But I fear it's madness ... even before testing it. **Solution B)** Add a TEXT field for the search and enter all possible combinations separated by the blank space " ", then define a FULLTEXT index. The problem are the stopwords that are not considered by the MATCH AGAINST query. I'd like to have some opinions and maybe some better ideas. Thank you.

I need to be able to search for **compound words** like "*menneskerettighedsforkæmperens*" (the human rights activist's) and find the words "*menneske*" (human), "*rettighed*" (right) and "*forkæmper*" (activist). I have all the words (and hundreds of thousands of other words) listed in MySQL, all in separate rows with additional information about each word (like hyphenation, which is what I need the DB to return), but I need to search a lot of words at the same time, which can be painfully slow. I'm currently using a MySQL database, but willing to switch it out for something better suiting my needs, maybe some kind of NoSQL or elasticsearch. But I haven't been able to find any examples of how to accomplish what I'm looking for in any other type of databases. So if anyone can help me out, I would really appreciate it.

When trying to deliver a snapshot for the transactional replication from SQL Server database to an old Azure SQL database (the database created long before Microsoft introduced Full Text support in Azure SQL) I get the following error: Command attempted: if databasepropertyex(db_name(),'IsFullTextEnabled') = 0 and isnull(is_member('db_owner'),0) = 1 exec sp_fulltext_database 'enable' (Transaction sequence number: xxxxxx, Command ID: xxxxx) Error messages: DBCC command 'CALLFULLTEXT' is not supported in this version of SQL Server. (Source: MSSQLServer, Error number: 40518) I noticed that for very old Azure SQL databases the **IsFullTextEnabled** flag is set always to **0** despite having the Full Text capabilities working just fine. This is in contrast to newly created Azure SQL databases, which always get mentioned flag set to **1** by default. Looks like a bug in the replication logic present in SQL Server (I checked 2019 and 2022 versions so far), because that flag is deprecated in Azure SQL and should never be evaluated by new logic, yet SQL Server replication still tries to evaluate it. Does anyone know how to overcome this issue and make the replication from SQL Server to Azure SQL work together with full text indexes? For now it looks like the only viable solutions, I can think of, are: 1. Do not replicate full text indexes at all to such old Azure databases. 2. Create a brand new database and replicate to it leaving the old one no longer used. However both approaches are not perfect and have some disadvantages. Can I somehow change the **IsFullTextEnabled** flag to **1** for such old Azure SQL databases or force the replication logic to not evaluate it at all?

**Background**: I've been using Postgres full-text search and it has met my needs quite well. Though there is some unexpected behavior that I cannot seem to wrap my head around. It has to do with the full-text search results returning the highlighted matches using the ts_headline function. It returns the correct matches most of the time, but often it will not return a match as I expect it. I think an example is the best way to demonstrate this. Relevant Postgres full-text highlighting docs: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-HEADLINE **Versions**: I have tried Postgres 15 and 12 and experienced this bug (feature?) in both. **Examples**: In the following 3 full-text search highlighting queries, why do the first one's results not match the second and third's? Im trying to figure out what I can do to get matches on the first query. I first noticed that some of my highlight queries were coming back with no 'hits'. I created the following 3 queries to show the issue I'm having. According to the docs, when there are no matches (identified by tags) it simply returns the first MinWords. That's what is happening in the first query when I think that we should actually get the 2 results back as we do in the following two queries.

postgres=# SELECT ts_headline('english', 'beginning word word word word CHILD word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word SERVICE ending', 
    to_tsquery('english', 'CHILD & SERVICE'), 
    'MaxFragments=2,MinWords=5,MaxWords=10');

          ts_headline          
-------------------------------
 beginning word word word word
(1 row)

Now let's increase MaxWords, now we get 2 matches

postgres=# SELECT ts_headline('english', 'beginning word word word word CHILD word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word SERVICE ending', 
    to_tsquery('english', 'CHILD & SERVICE'), 
    'MaxFragments=2,MinWords=5,MaxWords=11');

                                                      ts_headline                                                       
------------------------------------------------------------------------------------------------------------------------
 beginning word word word word CHILD word word word word word ... word word word word word SERVICE ending
(1 row)

Now let's increase MaxFragments, now we get 2 matches

postgres=# SELECT ts_headline('english', 'beginning word word word word CHILD word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word word SERVICE ending', 
    to_tsquery('english', 'CHILD & SERVICE'), 
    'MaxFragments=3,MinWords=5,MaxWords=10');

                                               ts_headline                                               
---------------------------------------------------------------------------------------------------------
 word word word word CHILD word word word word word ... word word word word SERVICE ending
(1 row)

I feel like something subtle is going on between all the MaxFragments, MinWords, MaxWords settings, or maybe this is undefined behavior or a bug. Im hoping to find a way to get the first query to match as I do believe it should. Please correct me if I'm wrong.

What we're running into: --------------------- Engine: MySQL. We've been working on a filter where an user can search for a reference number. However, we've ran into an issue with performance on bigger data sets. We need to search for partial words; say we have the reference AB12345678 - the user wants to find this reference by either searching AB, B123, 1234 or any other combination which contains part of this string. We have an INDEX on reference & date We're currently using LIKE %STRING%, but this cannot be indexed - and performance is bad in certain situations: **(1)** When we search for AB we get a fast result. **(2)** When we search for AB12345678 we get a slow result. Both situations have ORDER BY date & LIMIT. When we turn off either ORDER BY or LIMIT with situation **(2)**, we also get a fast result. A slow result is around 14-15 seconds of query execution time. The dataset contains around 300k results. What we've tried so far: ---------------------- We tried implementing FULLTEXT indexes and MATCH .. AGAINST queries, however MATCH .. AGAINST doesn't allow us to search for both sides, the * wildcard is only allowed at the end of the input string. We've also tried removing the INDEX on date. This gave us faster results (around 1/3th of the time) however, the query still took around 4-5 seconds. We're kinda lost on the best implementation that can improve our performance on this query and how we can best solve it right now. What method should we use to get our performance back and be able to search on both sides? Below is part our query which has slow results (we removed the SELECT part): SELECT SQL_NO_CACHE * FROM orders o0_ WHERE 1=1 AND o0_.customer_id = 130 AND (o0_.reference LIKE '%AB12345678%') ORDER BY date4 DESC LIMIT 50 OFFSET 0;

I am having problem with mysql fulltext search. When I change the order of keyword, the search speed changes. For example I have 4 queries, all of them return same result: 1. FAST (1s) SELECT * FROM table WHERE (MATCH (column_data) AGAINST('+english* +php*' IN BOOLEAN MODE) 2. SLOW (10s) SELECT * FROM table WHERE MATCH (column_data) AGAINST('+php* +english*' IN BOOLEAN MODE) 3. FAST (1s) SELECT * FROM table WHERE MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE) AND (MATCH (column_data)) AGAINST('+english*' IN BOOLEAN MODE)) 4. SLOW (10s) SELECT * FROM table WHERE (MATCH (column_data) AGAINST('+english*' IN BOOLEAN MODE)) AND (MATCH (column_data) AGAINST('+php*' IN BOOLEAN MODE)) How can I optimize it?