netsuvi netsuvi - 4 months ago 14
SQL Question

Optimize sql query with LIKE %var%

The query runs fast on my local dev machine, but not on the webserver. Any idea to optimize this query?

SELECT * from mybigtable
WHERE title LIKE %.'$var'.% AND lang='.$lang.'
ORDER BY score DESC
LIMIT 6;

Answer

You can use database dependent optimizations

MySQL>> ALTER TABLE articles ADD FULLTEXT(title);
MySQL>> SELECT * FROM articles WHERE MATCH(title) AGAINST ('TEXTVALUE')

MS-SQL>> SELECT ProductName FROM Products WHERE FREETEXT (ProductName, 'spread' )

 PgSQL>> CREATE FUNCTION fti() RETURNS opaque AS '/path/to/fti.so' LANGUAGE 'C';
PgSQL>> CREATE TABLE articles_fti (string type, id oid);

Oracle >>  CONTAINS function