CODEWITHSUNDEEP
phpmysqlsqlsearch
Buffer Overflow
Buffer Overflow

Reputation: 1046

Optimize MySQL search Query

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?

Upvotes: 0

Views: 228

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

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;

Upvotes: 2

Related Questions