I've a bit older project. It uses mysql 5.5.59. In the beginning, with only some rows in the table, the performance was good. But now the table has about 80.000 rows (imho: it's still very small).
But actually we face some performance problems so my task is to optimize SQL. I figured outsomething very curious which I cannot explain: UNION is faster than OR!
Here's my simplyfied OR statement:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS field1, field2, field3
FROM tablename
WHERE field1 = 'some_value' OR field2 = 'some_value'
ORDER BY field3;
And this is my simplyfied UNION statement:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS *
FROM (
SELECT field1, field2, field3
FROM tablename
WHERE field1 = 'some_value') a1
UNION (
SELECT field1, field2, field3
FROM tablename
WHERE field2 = 'some_value')
ORDER BY field3;
Theres an index on field1 and field2.
I testet on mysql-cli with SQL_NO_CACHE to block the query cache from mysql and got following results: the UNION statement needs 0.00 sec, the OR-Statement needs 0.21 sec
Can anyone eyplain this behavior? Or is there a way to optimize an OR-Statement?
