Optimize MySQL search Query

Question

I just tried to do some small search for my website to find user entries and sort them in a logical way, I tried improving the results, but it ended up with a way too big query string, which takes 14 seconds to execute on my Server. So I'm currently wondering how to optimize the query without any bigger changes in the result.

Here is my current query:

$query = "SELECT * FROM(
    SELECT 1 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE title LIKE '".$search."'
            union all
    SELECT 2 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE title LIKE '".$search."%'
        OR title LIKE '%".$search."'
            union all
    SELECT 3 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE title LIKE '%".$search."%'
            union all
    SELECT 4 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE keywords LIKE '".$search."'
            union all
    SELECT 5 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE keywords LIKE '%".$search."'
        OR keywords LIKE '".$search."%'
            union all
    SELECT 6 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE keywords LIKE '%".$search."%'
            union all
    SELECT 7 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE content LIKE '".$search."'
            union all
    SELECT 8 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE content LIKE '%".$search."'
        OR content LIKE '".$search."%'
            union all
    SELECT 9 as m_rank,title,rank, content, url, keywords FROM websites
        WHERE content LIKE '%".$search."%'
    ) a
    ORDER BY m_rank, rank DESC LIMIT ".$page.", ".$max;

Are there also possibilities to cut the query using PHP for pre-processing?

Other alternatives to perhaps look into: FULLTEXT searches (dev.mysql.com/doc/refman/5.0/en/fulltext-search.html) or implementing something like Apache Lucene (lucene.apache.org/solr) rather than an RYO solution. — CD001
– CD001, Commented Jan 17, 2014 at 15:43

Gordon Linoff · Accepted Answer · 2014-01-17 15:19:24Z

2

You can do this with one query. Put the different search conditions in a case to assign m_rank. Something like:

SELECT title, rank, content, url, keywords,
       (case when title LIKE '".$search."' then 1
             when title LIKE '".$search."%' OR title LIKE '%".$search."' then 2
             when title LIKE '%".$search."%' then 3
             . . .
             else NULL
        end) as m_rank
FROM websites
HAVING m_rank is not null
ORDER BY m_rank, rank DESC
LIMIT ".$page.", ".$max;

answered Jan 17, 2014 at 15:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CD001 Over a year ago

nice - using CASE (dev.mysql.com/doc/refman/5.0/en/case.html) is much neater than some of the IF behemoths I've written in the past :)

Collectives™ on Stack Overflow

Optimize MySQL search Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related