Forums.ATC.no

Teknisk => Generelt teknisk => Emne startet av: ATC på 27. ſeptember 2008, 18:24 pm

Tittel: Mysql: Optimize OR clauses using UNION
Skrevet av: ATC på 27. ſeptember 2008, 18:24 pm

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.

Tittel: [Solved] Mysql: Optimize OR clauses using UNION
Skrevet av: ATC på 27. ſeptember 2008, 18:24 pm

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