0

OK, sorry for the odd title, but I think it covers it.

I'm trying to increase performance of my CV search database. 30,000 records and growing and we are seeing some performance issues.

I have created an index of the field that is slowing things down, which is the body of text of their CV(All duplicate words and stop word already removed).

I created a fulltext index of that field and it works GREAT on single word queries like 'DRILLER', or two word queries like 'DRILLER AND PAINTER'. I can see on the EXPLAIN that the index is firing, and things are fast - happy days.

My question. When I perform a search say 'DRILLER OR CLEANER' then the index doesn't fire, and the query time drags out to 6 or so seconds. My SQL queries;

FAST - uses index

SELECT nl_id FROM CV 
 WHERE (  ( MATCH (nl_striptext) AGAINST ('driller' IN BOOLEAN MODE)
       AND  MATCH (nl_striptext) AGAINST ('cleaner' IN BOOLEAN MODE) ) )

SLOW - index not firing

SELECT nl_id FROM CV
 WHERE (    ( MATCH (nl_striptext) AGAINST ('driller' IN BOOLEAN MODE)
         OR   MATCH (nl_striptext) AGAINST ('cleaner' IN BOOLEAN MODE) ) )
 LIMIT 15 OFFSET 0

I'm scratching my head here, and your help is appreciated.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
spankymac
  • 746
  • 2
  • 7
  • 14
  • Yes the OR clause in the 2nd query is killing it check here some solutions like using UnION etc http://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause http://stackoverflow.com/questions/7110964/correct-indexing-when-using-or-operator – Abhik Chakraborty Jun 26 '14 at 11:00
  • thanks, that's a helpful start! – spankymac Jun 26 '14 at 11:20

2 Answers2

0

OR can cause problems with the optimization of queries. You can fix this by writing the query as:

(SELECT nl_id
 FROM CV
 WHERE MATCH (nl_striptext) AGAINST ('driller' IN BOOLEAN MODE)
) UNION
(SELECT nl_id
 FROM CV
 WHERE MATCH (nl_striptext) AGAINST ('cleaner' IN BOOLEAN MODE)
)
LIMIT 15 OFFSET 0;

With a LIMIT clause, you should use ORDER BY. Otherwise the results are indeterminate, because the results of the query are in no guaranteed order.

Also, note the use of UNION. This will remove duplicates where both words appear.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your answer, you too Ollie! The issue I have is that I am using a 3rd party tool that writes the querystring for me. I struggled for a long time for a function that would turn a user writing 'driller OR cleaner' into a boolean sql query. I turned the internet upside looking for a function in .net that would write the sql for me. I thought it would be common, I guess not. – spankymac Jun 26 '14 at 11:31
  • I was OK writing my own on simple queries, but I was getting queries in like this; `("HSE" OR "quality control" OR "quality assurance" OR "qa" OR "qc") AND ("power generation" OR "power plant" OR "power station" OR "nuclear")` and was getting stuck. – spankymac Jun 26 '14 at 11:34
0

You have a couple of choices here, to fix this OR problem. Keep in mind that OR casts a wider net -- matches more things -- than AND does. AND narrows down your search and OR broadens it.

One is to take better advantage of the BOOLEAN MODE in your MATCH clause, by searching for both terms in a single search. 

MATCH (nl_striptext) AGAINST ('driller cleaner' IN BOOLEAN MODE)

This is probably your best bet. It's fast.

You can also use the SQL UNION of two searches to implement your OR operation. Gordon offers that approach in another answer.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Ollie, do you know of a function that can convert user search input, into the sql query? – spankymac Jun 26 '14 at 14:09
  • That should be a different question; this one is about sql optimization. – O. Jones Jun 26 '14 at 15:40
  • Ollie, I asked the [question a few months back and had no replies](http://stackoverflow.com/questions/22177321/trying-to-convert-search-query-into-mysql-in-boolean-mode-using-asp-net). I have also turn the internet upside down looking for a solution. I thought it would be a common challenge. But I must be the only person on the planet who is trying to convert user input into MYSQL friendly querires. – spankymac Jun 26 '14 at 16:12