OR SQL operator in mysql and slow query
….not anymore, if you use IF.
there are cases where you can get rid of OR and get better performance in mysql.
here is a case a table:
book
id
french_name
engl_name
the books have only one name and only one, the other being null
you have a form on your web page and want to do a search for the books that contain a certain substring of characters. the client wants to retrieve both english and french name that contain that substring.
first shot: select * from book where french_name like ‘%CHARS%’ or engl_name like ‘%CHARS%’
this is very expensive.
the better solution:
select * from book where if(french_name is null,engl_name,french_name) like ‘%CHARS%’
on my system is 2 times faster.
Cool, I’ll try this out.