Can anyone work out a more optimised SQL solution?

Question

I am worried this is a bit expensive.

Plus I will soon implement a normalized system for the tags so there will be additional joins.

On top of that I have 4 tables (tbl_videos, tbl_articles, tbl_galleries and tbl_users) of which I want to display three results of each and thus will have to run the query four times on one press of 'search'.

SELECT *, 
(
(CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) +

(CASE WHEN `tags` LIKE '%hotel%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%london%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%lazy%' THEN 1 ELSE 0 END) + 
(CASE WHEN `tags` LIKE '%dog%' THEN 1 ELSE 0 END)

) AS relevance
FROM `table`
WHERE `description` LIKE '%hotel%'
  OR `description` LIKE '%london%'
  OR `description` LIKE '%lazy%'
  OR `description` LIKE '%dog%' 
  OR `title` LIKE '%hotel%'
  OR `title` LIKE '%london%'
  OR `title` LIKE '%lazy%'
  OR `title` LIKE '%dog%'
  OR `tags` LIKE '%hotel%'
  OR `tags` LIKE '%london%'
  OR `tags` LIKE '%lazy%'
  OR `tags` LIKE '%dog%'
ORDER BY relevance DESC
LIMIT 0 , 3;

Answer 1

Any idea how to optimise it. LIKE for the whole table will not be fast. To be honest I am afraid it will be slow...

Of course it depends on size of data and database server resources.

But you are using MySQL, maybe Full Search Text would be better in this case?

Answer 2

Yes, this is probably going to be quite resource-hungry, but it sounds like you have a business layer in front of it before you submit the query.

Think about what you want to achieve, and what you could do yourself, in terms of parsing out search terms, or even giving the user separate fields for description, title and tags, and construct the query appropriately, to more direct the query, rather than effectively saying, "I've got some data quite like this search term, somewhere in the columns this table".

Once you have settled on your business logic (and if the above query is how it ends up, although I doubt it, then sobeit), then you should definitely run the query through the explain plan and see where you might pick up some indexes to help the database along.

EDIT:

Okay, so how's this for a suggestion

select matched_val, relevance from (
    select description as matched_val, count(*) as relevance
    from table 
    where description like '%hotel%'
    or description like '%london%'
    or description like '%lazy%'
    or description like '%dog%'
    group by description

    union all

    select title as matched_val, count(*) as relevance
    from table 
    where title like '%hotel%'
    or title like '%london%'
    or title like '%lazy%'
    or title like '%dog%'
    group by title

    union all

    select tags as matched_val, count(*) as relevance
    from table 
    where tags like '%hotel%'
    or tags like '%london%'
    or tags like '%lazy%'
    or tags like '%dog%'
    group by tags
) as a
order by a.relevance desc
LIMIT 0 , 3

it at least means you only have to do your like matching once, rather than in the predicate and in the switch statement, plus the optimiser would be able to use an index on description, title and tags (which you will need to add yourself) and you should be away.

have a go and see how your query optimiser likes it...