Mysql: Optimize OR clauses using UNION

[ATC]

On large tables, a query such as the following one will optimize poorly and use the index for the OR'ed column inefficiently:

SELECT * FROM syslog
WHERE ( prog = 'sshd' OR prog = 'sshd(pam_unix)' )
AND   user = 'floyd'
ORDER BY timestamp DESC
LIMIT 10

The result is surprisingly poor performance, even if both the "prog" and "user" columns are properly indexed.

[ATC]

Rewrite using UNION:

(
  SELECT * FROM syslog
  WHERE prog = 'sshd'
  AND   user = 'floyd'
) UNION (
  SELECT * FROM syslog
  WHERE prog = 'sshd(pam_unix)'
  AND   user = 'floyd'
)
ORDER BY timestamp DESC
LIMIT 10

The resulting dataset is exactly the same, but now you're telling MySQL explicitly to look for two distinct combinations of data. This lets MySQL use the index for "prog" more efficiently and the result is a significant performance boost.

Note that UNION was implemented in MySQL 4.x