Databases
StackOverflow2013
Postsdbo
POMySQL Optimize UNION query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL Optimize UNION query
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USuser1946784
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL Optimize UNION query
      1. USsacrepit
      1. VTFavorite
    2. VO
      singulars
      1. POMySQL Optimize UNION query
      1. This table or related slice is empty.
      1. VTUpMod
    1. COUnion ALL doesn't use Index so I don't think you can optimize too much.
      singulars
      1. POMySQL Optimize UNION query
      1. USjcho360
    2. COHow is priority determined?
      singulars
      1. POMySQL Optimize UNION query
      1. USCharles Burns
    3. COCharles - thanks. Currently, the priority is defined by the application itself. Theoretically it's dynamic, but in reality it's "more or less" static, so it could go to the database (actually see my one of my comments below). I'll try to put the prio in the database and use an index+limit+order by
      singulars
      1. POMySQL Optimize UNION query
      1. USuser1946784
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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