Databases
StackOverflow2013
Postsdbo
POMySQL performance differences between a different "FROM" operator usage
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL performance differences between a different "FROM" operator usage
    text
    copied!<p>Can someone please explain to me why this:</p> <pre><code>SELECT A.id, A.name, B.id AS title_id FROM title_information AS A JOIN titles B ON B.title_id = A.id WHERE A.name LIKE '%testing%' </code></pre> <p>is considerably slower (6-7 times) than this:</p> <pre><code>SELECT A.id, A.name, B.id AS title_id FROM (SELECT id, name FROM title_information) AS A JOIN titles B ON B.title_id = A.id WHERE A.name LIKE '%testing%' </code></pre> <p>I know it's probably hard to answer this question without knowing full details about the schema and MySQL configuration, but I'm looking for any generic reasons why the first example could be so significantly slower than the second?</p> <p>Running EXPLAIN gives this:</p> <pre><code>|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* || || 1 || SIMPLE || B || index || || id || 12 || || 80407 || Using index || || 1 || SIMPLE || A || eq_ref || PRIMARY,id_UNIQUE,Index 4 || PRIMARY || 4 || newsql.B.title_id || 1 || Using where || </code></pre> <p>and</p> <pre><code>|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* || || 1 || PRIMARY || B || index || || id || 12 || || 80407 || Using index || || 1 || PRIMARY || &lt;derived2&gt; || ALL || || || || || 71038 || Using where; Using join buffer || || 2 || DERIVED || title_information || index || || Index 4 || 206 || || 71038 || Using index || </code></pre> <p><strong>UPDATE:</strong> A.id and B.id are both PRIMARY KEYS, while A.name is an index. Both tables have around 50,000 rows (~15MB). MySQL configuration is pretty much a default one.</p> <p>Not sure if that helps (or if it adds more to the confusion - as it does for me) but using more generic LIKE statement that is likely to have more matching fields (e.g. <em>"LIKE '%x%'"</em>) makes the first query run considerably faster. On the other hand, using <em>"LIKE '%there are no records matching this%'"</em> will make the second query a lot faster (while the first one struggles).</p> <p>Anyone can shed some light on what's going on here?</p> <p>Thank you!</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