how can i make query more faster

Question

I have one complex queries and which fetches data from database based on search keywords. I have written two query to fetch data based on keyword by joining two tables. And each table contains more than 5 millions of records. But the problem is, this query takes 5-7 seconds to run so the page take more time to laod. The queries are:

SELECT DISTINCT( `general_info`.`company_name` ), 
               general_info.* 
FROM   general_info 
       INNER JOIN `financial_info` 
               ON `financial_info`.`reg_code` = `general_info`.`reg_code` 
WHERE  ( `financial_info`.`type_of_activity` LIKE '%siveco%' 
          OR `general_info`.`company_name` LIKE '%siveco%' 
          OR `general_info`.`reg_code` LIKE '%siveco%' )

Any time you try to do LIKE '%word%', you will have poopy performance — Kermit
– Kermit, Commented Sep 23, 2014 at 15:35
Alternative ideas: MySQL fulltext search functions or NoSQL with MapReduce. — Daniel W.
– Daniel W., Commented Sep 23, 2014 at 15:41

Gordon Linoff · Accepted Answer · 2014-09-23 15:39:23Z

The parentheses around distinct don't make a difference. distinct is not a function. So your query is equivalent to:

SELECT gi.* 
FROM   general_info gi INNER JOIN
      `financial_info` gi
       ON fi.`reg_code` = gi.`reg_code` 
WHERE fi.`type_of_activity` LIKE '%siveco%' OR
      gi.`company_name` LIKE '%siveco%' OR
      gi.`reg_code` LIKE '%siveco%';

For the join, you should have indexes on general_info(reg_code) and financial_info(reg_code). You may already have these indexes.

The real problem is probably the where clause. Because you are using wildcards at the beginning of the pattern, you cannot optimize this with a regular index. You may be able to do what you want using full text search, along with the matches clause. The documentation for such an index is here. This will work particularly well if you are looking for complete words in the various names.

+1. DISTINCT is a keyword that applies to the entire SELECT list; wrapping an expression in parens makes no difference. The DISTINCT will have an effect (on the number of rows returned) if there are multiple rows in financial_info that match a given row in general_info (i.e. if reg_code is not unique in financial_info.

Collectives™ on Stack Overflow

how can i make query more faster

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related