Search code examples
mysqloptimizationsql-like

Query Optimization Mysql

I am using this query for searching, but the query is slow. Can anyone suggest me an alternative for this query?

SELECT c.ctgr_name,n.lgcode,n.newsID,n.newsTITLE,n.image3,n.ctgr_code 
FROM tbnews  n LEFT JOIN tbcategory c ON c.ctgr_code=n.ctgr_code  
WHERE (newsTITLE LIKE "% Foray %" OR image3 LIKE "%Foray%" OR video3 LIKE "%Foray%" 
OR newsTITLE LIKE "% Foray %" OR image3 LIKE "%Foray%" OR video3 LIKE "%Foray%" ) 
AND (n.publish!=0) 
ORDER BY newsID DESC LIMIT 10
Solution

  • 1) Always run EXPLAIN SELECT before asking for optimization. Stick "EXPLAIN" keyword before your query and then run it - MySQL will tell you what it wants to do and you'll see immediately where it gets stuck and where it doesn't use indexes.

    2) Using LIKE '%%' searches (with percentage sign before AND after keyword) forces MySQL to do full table scan. Using a search such as LIKE 'keyword%' MIGHT make MySQL use the index on that field, if the index exists.

    3) Configure your MySQL instance, tweaking the config and using appropriate storage engine can boost performance by a huge margin. By default, MySQL is configured to work on very slow machines and use very little resources - hence, when a table gets slightly larger (say, 100k rows), people notice performance drop. MySQL can cope with large tables easily, if configured properly.