StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The column alias <code>last</code> (in your query) cannot be referenced in the WHERE clause (of your query). This is a MySQL syntax rule. (The reason for the rule, basically, is that the expression referenced by that alias is not available at the time the predicates in the WHERE clause are processed.)</p> <p>But that column alias CAN be referenced in a predicate in a <code>HAVING</code> clause. The <code>HAVING</code> clause gets processed almost at the end of the execution plan. This works, but is problematic from a performance standpoint:</p> <pre><code>SELECT from_unixtime( `lastlogin` ) AS `last` FROM `players` WHERE `lastlogin` <> 0 HAVING `last` < '2012-10-01 00:00:00'; </code></pre> <p>The execution plan for that is nearly equivalent to using an inline view (to create a derived table) and running a SELECT against the derived table (which is also problematic from a performance standpoint):</p> <pre><code>SELECT d.* FROM ( SELECT from_unixtime( `lastlogin` ) AS `last` FROM `players` WHERE `lastlogin` <> 0 ) d WHERE `last` < '2012-10-01 00:00:00'; </code></pre> <p>The column alias <code>last</code> can be referenced here, because the inner query (the inline view aliased as d) gets run first, and the resultset gets stored as a MyISAM table. Then the outer query runs against the MyISAM table. And in this query, the <code>last</code> is referencing a column name from a table, so it can be referenced in a WHERE clause.</p> <p>IMPORTANT NOTE:</p> <p>So that sort of answers the question you asked. But, </p> <p>You don't really want to do that!</p> <p>This approach will exhibit problematic performance for large sets, since this query is essentially creating a copy of the table, and then running a query on the copy. That from_unixtime function is going to be performed for EVERY row in the table (except for the rows where the lastlogin is NULL or equal to zero.) </p> <p>From a performance standpoint, it's much better to use a single query (no inline views) and use a predicate on the bare column in the WHERE clause.</p> <p>Ideally, <code>lastlogin</code> would be stored as DATETIME or TIMESTAMP datatype. If, however, that column is an integer, then it would be much better (performance wise) to convert the literal constant in the predicate into an integer, and then compare that to the bare column. This will allow MySQL to at least consider doing a range scan on an index that has <code>lastlogin</code> as a leading column, rather than performing the <code>from_unixtime</code> function on every row, to compare to the literal.</p> <p>MySQL provides a convenient <code>UNIX_TIMESTAMP()</code> function for converting a DATETIME into an integer value (it's basically the inverse of the <code>FROM_UNIXTIME()</code> function, with a few caveats.</p> <p>For best performance, you want a query of the form:</p> <pre><code>SELECT from_unixtime( `lastlogin` ) AS `last` FROM `players` WHERE `lastlogin` <> 0 AND `lastlogin` < UNIX_TIMESTAMP('2012-10-01 00:00:00') </code></pre>

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