Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The first syntax is generally more efficient.</p> <p><code>MySQL</code> buffers the derived queries so using the derived query robs the <code>user_profile</code> of possibility to be a driven table in the join.</p> <p>Even if the <code>user_profile</code> is leading, the subquery results should be buffered first which implies a memory and performance impact.</p> <p>A <code>LIMIT</code> applied to the queries will make the first query much faster which is not true for the second one.</p> <p>Here are the sample plans. There is an index on <code>(val, nid)</code> in the table <code>t_source</code>:</p> <p>First query:</p> <pre><code>EXPLAIN SELECT * FROM t_source s1 JOIN t_source s2 ON s2.nid = s1.id WHERE s2.val = 1 1, 'SIMPLE', 's1', 'ALL', 'PRIMARY', '', '', '', 1000000, '' 1, 'SIMPLE', 's2', 'ref', 'ix_source_val,ix_source_val_nid,ix_source_vald_nid', 'ix_source_val_nid', '8', 'const,test.s1.id', 1, 'Using where' </code></pre> <p>Second query:</p> <pre><code>EXPLAIN SELECT * FROM t_source s1 JOIN ( SELECT nid FROM t_source s2 WHERE val = 1 ) q ON q.nid = s1.id 1, 'PRIMARY', '&lt;derived2&gt;', 'ALL', '', '', '', '', 100000, '' 1, 'PRIMARY', 's1', 'ref', 'PRIMARY', 'PRIMARY', '4', 'q.nid', 10000, 'Using where' 2, 'DERIVED', 's2', 'ref', 'ix_source_val,ix_source_val_nid,ix_source_vald_nid', 'ix_source_vald_nid', '4', '', 91324, 'Using index' </code></pre> <p>As you can see, only a part of the index is used in the second case, and <code>q</code> is forced to be leading.</p> <p><strong>Update:</strong></p> <p><em>Derived queries</em> (which is what this question concerns) are not to be confused with the <em>subqueries</em>.</p> <p>While <code>MySQL</code> is not able to optimize <strong>derived queries</strong> (those used in the <code>FROM</code> clause), the <strong>subqueries</strong> (those used with <code>IN</code> or <code>EXISTS</code>) are treated much better.</p> <p>See these articles in my blog for more detail:</p> <ul> <li><a href="http://explainextended.com/2009/08/18/passing-parameters-in-mysql-in-list-vs-temporary-table/" rel="noreferrer"><strong>Passing parameters in MySQL: IN list vs. temporary table</strong></a></li> <li><a href="http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/" rel="noreferrer"><strong>NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL</strong></a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POWhich MySQL JOIN query is more efficient?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USQuassnoi
    1. USQuassnoi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhich MySQL JOIN query is more efficient?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO+1 for a detailed answer, and thank you for the heads-up on the ridiculous failings of the MySQL optimizer; I've had issues with it before, but nothing quite that poor.
      singulars
      1. PO
      1. USJames McNellis
 

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