CODEWITHSUNDEEP
mysqlquery-optimization
Mohamedali Jinna
Mohamedali Jinna

Reputation: 11

How to make mysql query fast while searchs with like

I have three table and I have to search them with a like match. The query runs over 10,000 records. It works fine but take 4 seconds to give results. What can I do to improve the speed and take it down to 1 second?

profile_category_table
----------------------
restaurant
sea food restaurant

profile_keywords_table
----------------------
rest
restroom
r.s.t

company_profile_table
---------------------
maha restaurants
indian restaurants

Query:

SELECT name
FROM (
        (SELECT PC_name AS name
         FROM profile_category_table
         WHERE PC_status=1
           AND PC_parentid!=0
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(PC_name)) LIKE '%rest%')
         GROUP BY PC_name)
      UNION
        (SELECT PROFKEY_name AS name
         FROM profile_keywords_table
         WHERE PROFKEY_status=1
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(PROFKEY_name)) LIKE '%rest%')
         GROUP BY PROFKEY_name)
      UNION
        (SELECT COM_name AS name
         FROM company_profile_table
         WHERE COM_status=1
           AND (regex_replace('[^a-zA-Z0-9\-]','',remove_specialCharacter(COM_name)) LIKE '%rest%')
         GROUP BY COM_name))a
ORDER BY IF(name LIKE '%rest%',1,0) DESC LIMIT 0, 2

And I add INDEX FOR THAT columns too.

if a user search with text rest in textbox..the auto suggestions results should be..

results

restaurant
sea food restaurant
maha restaurants
indian restaurants
rest
restroom
r.s.t

i used regex_replace('[^a-zA-Z0-9-]','',remove_specialCharacter(COM_name) to remove special characters from the field value and to math with that keyword..

Upvotes: 0

Views: 2091

Answers (4)

Adrian Shum
Adrian Shum

Reputation: 40076

There are lots of thing you can consider:

The main killer of performance here is probably the regex_replace() ... like '%FOO%'. Given that you are applying function on the columns, indices are not going to take effect, leaving you several full table scans. Not to mention regex replace is going to be heavy weight. For the sake of optimization, you may

  1. Keep a separate column, which stored the "sanitized" data, for which you create indices on, and leaving your query like where pc_name_sanitized like '%FOO%'
  2. I am not sure if it is available in MySql, but in a lot of DMBS, there is a feature called function-based index. You can consider making use of it to index the regex replace function

However even after the above changes, you will find the performance is not very attractive. In most case, using like with wildcard at the front is avoiding indices to be used. If possible, try to do exact match, or have the beginning of string provided, e.g. where pc_name_sanitized like 'FOO%'

As mentioned by other users mentioned, using UNION is also a performance killer. Try to use UNION ALL instead if possible.

Upvotes: 1

DB_learner
DB_learner

Reputation: 1026

Since you use union in between all queries, you can remove the group by option in all queries and you select only column having "rest" in it. so remove the function "IF(name LIKE '%rest%',1,0)"in the order by clause.

Upvotes: 0

Mathieu Amiot
Mathieu Amiot

Reputation: 1214

Rewrite it completely. UNION statements are killing performance, and you're doing the LIKE on too many fields. Moreover you're searching into a temporary table (SELECT field FROM (...subquery...)), so without any indexes, which is really slow (1/1 chance to go through full-table scan for each row).

Upvotes: 0

Adrian
Adrian

Reputation: 1370

I'm going to say don't filter on the query. Do that on whatever language you're programming in. Regex_replace is a heavy operation regardless of the environment and you're doing this several times on a query of 10,000 records with a union of who knows how many more.

Upvotes: 0

Related Questions