vendredi 26 juin 2015

comparing IF statement and CASE statement in mysql

I have a table included two columns. something like this:

// mytable
+----+---------------+
| id |      word     |
+----+---------------+
| 1  | hello         |
| 2  | next to       |   
| 3  | pay attention |
| 4  | turn down     |
+----+---------------+

The column of word is both index and full-text. I want to search in word in first as index and if nothing is found, then search as full-text. Now I Implemented the query in two ways:

One: (via If-Else statement)

IF ((select COUNT(*) from mytable where word like '%anything%') < 1)
     select word from mytable where word like '%anything%'
ELSE IF 
     select word from mytable where match(word) against('anything');

Two: (via CASE statement)

SELECT CASE 
   WHEN (select COUNT(*) from mytable where word like '%anything%') < 1
       THEN select word from mytable where word like '%anything%' 
   WHEN (select COUNT(*) from mytable where match(word) against('anything'))  > 0
       THEN select word from mytable where match(word) against('anything') 
   END 
from mytable  

Now I want to know which one is more faster and optimized ?

Aucun commentaire:

Enregistrer un commentaire