StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Tables are not necessarily joined in the order that they are specified in the <code>FROM</code> clause. In this case, it looks like MySQL has decided that in the absence of a <code>WHERE</code> clause in the query, it's probably fastest to scan the users table first, and then join that with the other tables.</p> <p>The first thing I would do would be to run <code>ANALYZE TABLE</code> on all three tables involved in the query. This updates table statistics and stored key distributions, and allows the join optimizer to make better decisions. Run the <code>EXPLAIN</code> statement afterwards and see if it has changed.</p> <p>If it hasn't changed, you might need to resort to using the <code>STRAIGHT_JOIN</code> keyword. This forces the join optimizer to join the tables in the exact order specified in the query. To help determine if you should do this, you're supposed to take the product of all the <code>rows</code> values from an <code>EXPLAIN</code> result, and compare it to the actual number of rows returned from the query. So in this case, compare 1629 (181x9x1) to the actual number of rows. If they are significantly different, a <code>STRAIGHT_JOIN</code> might be called for (used as a keyword to <code>SELECT</code>, ie. <code>SELECT STRAIGHT_JOIN n.nid</code>... etc).</p> <p>As an aside, there is a way to <a href="http://dev.mysql.com/doc/refman/5.0/en/index-hints.html" rel="nofollow noreferrer">tell MySQL to use a specific index</a>, but I don't think it would work for your user table in this query as it is right now, since there is no <code>WHERE</code> clause. If you end up using <code>STRAIGHT_JOIN</code>, you might possibly need it, but in that case MySQL would likely pick up on the primary key if the user table was not the first table in the join.</p> <p>You should have a look at the <a href="http://dev.mysql.com/doc/refman/5.0/en/using-explain.html" rel="nofollow noreferrer">EXPLAIN syntax page</a> for more helpful details on this as well.</p> <p>This query doesn't look like it should be <em>that</em> slow for what it is. Without a where clause, you can expect a full-table scan somewhere, and MySQL has kept it down to about 1700 total rows examined. It seems like this would only be a problem if it was a high-use query, in which case you might want to examine the underlying architecture that (without a <code>WHERE</code> clause) involves running a query which will affect every user in the system, and will only get heavier as more users are added.</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