StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL Optimize UNION query
text
Body
copied!<p>I'm trying to optimize a query.</p> <p>My question seems to be similar to <a href="https://stackoverflow.com/questions/4008486/mysql-union-all-and-limit">MySQL, Union ALL and LIMIT</a> and the answer might be the same (I'm afraid). However in my case there's a stricter limit (1) as well as an index on a datetime column.</p> <p>So here we go:</p> <p>For simplicity, let's have just one table with three: columns: </p> <ul> <li>md5 (varchar) </li> <li>value (varchar). </li> <li>lastupdated (datetime)</li> </ul> <p>There's an index on (md5, updated) so selecting on a md5 key, ordering by updated and limiting to 1 will be optimized.</p> <p>The search shall return a maximum of one record matching one of 10 md5 keys. The keys have a priority. So if there's a record with prio 1 it will be preferred over any record with prio 2, 3 etc.</p> <p>Currently UNION ALL is used:</p> <pre><code>select * from ( ( select 0 prio, value from mytable where md5 = '7b76e7c87e1e697d08300fd9058ed1db' order by lastupdated desc limit 1 ) union all ( select 1 prio, value from mytable where md5 = 'eb36cd1c563ffedc6adaf8b74c259723' order by lastupdated desc limit 1 ) ) x order by prio limit 1; </code></pre> <p>It works, but the UNION seems to execute all 10 queries if 10 keys are provided. </p> <p>However, from a business perspective, it would be ok to run the selects sequentially and stop after the first match.</p> <p>Is that possible though plain SQL? </p> <p>Or would the only option be a stored procedure?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload