|
|
|
|
| Mysql: Optimize OR clauses using UNION |
|---|
|
Problem:
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.
|
|
Solution:
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
|
|
|
|
|
|
This is free technical advice without any warranties.
Use at your own risk. Solutions offered may not apply to your particular problem at all.
Opinions and views expressed in the articles are the authors' personal
and may not necessarily reflect or coincide with those of Ano-Tech Computers.
We take no responsibility if anything bad happens from following any of
the intructions given. If you don't understand and accept this, please
contact us for personal assistance instead.
To read our full disclaimer, go
here.
We welcome anyone who is willing to contribute to this public knowledge base,
contact siteadmin@atc.no
if you have information you would like to share. The idea is not to replace
the commercial support sites, but to publish those hard-to-find
solutions you've found yourself looking for over and over again.
Show all articles
