Performance of MySQL "AS" and not "AS"

Question

Is there a performance difference between the following two queries?

SELECT * FROM users AS u LEFT JOIN parents AS p ON u.id=p.user_id

and

SELECT * FROM users u LEFT JOIN parents p ON u.id=p.user_id

(the second one doesn't have the AS)

I think that quey have no performance difference,But thi first query is for better approach — Shijin TR, Mar 27 '13 at 08:54
Potential duplicate of http://stackoverflow.com/questions/8363108/how-does-table-alias-names-affect-performance — Martin, Mar 27 '13 at 08:55

RobIII · Accepted Answer · 2013-03-27T11:03:42.010

As with everything: to measure is to know. If you really want to know run a profiler and measure. Otherwise take it from us: it's not even worth the thought you have put in about it so far.

If there's any difference (of considerable amount) at all that should a) be considered a flaw in MySQL, b) be a problem during the query parsing part, not the query execution part and thus be negligable anyway since execution rather than parsing will usually account for 99.99999% of the total "querytime" anyway (if not more).

If there's any measurable difference, it ~~will~~ should be very, very, véry small and thus a) not be worth bothering about the difference since users don't usually notice speedups in increments of nanoseconds and b) only noticable when you run many, many thousands of queries in a tight loop.

If MySQL's contributers didn't mess up then there's no difference at all since the AS keyword is just optional and exists for legibility. The only way to know for sure is to profile. It won't be the first time some strange bug or behaviour pops up in software. But it is (very) safe to assume the AS keyword doesn't make a difference at all since that would have been 'discovered' a long time ago by any of the millions of MySQL users that have preceeded you (or none of them ever bothered, you never know Puh ). Spending any time profiling this "question" would, IMHO, be a waste of time.

Read the rules of Optimization club.

Only when there actually is a performance issue you start looking for places to optimize. And you only optimize "low hanging fruit" or stuff that accounts for considerable load (be it I/O, CPU, network, whatever). Optimizing queries removing AS keywords for performance is a microoptimization that will never, ever, return the investment of your time (barring bugs/issues). As a "thought experiment" though your question can (and should) be answered by establishing hard data using profiling rather than guesses, other people's opinions etc.

I did leave out stuff like querycaches that could potentially suffer from queries being executed with/without AS keywords or AS keywords in different places each time a query is executed which would (or rather: could) cause query execution plans to be (unnecessarily) be recreated or query caches unable being unable to be reused etc. but now we're really talking edge-cases here and for this the saying, again, goes: to measure is to know.

score 1 · Answer 2 · answered Mar 27 '13 at 08:55

1

The are really the same.

The AS keyword is optional. Just like OUTER keyword on LEFT JOIN and LEFT OUTER JOIN.

answered Mar 27 '13 at 08:55

John Woo

258,903
69
498
492

score 0 · Answer 3 · answered Mar 27 '13 at 08:56

The AS keyword is optional when aliasing a select_expr with an identifier. However, because the AS is optional. MySQL interprets the second as an alias name. For this reason, it is good practice to be in the habit of using AS explicitly when specifying column aliases.

by: http://dev.mysql.com/doc/refman/5.0/en/select.html#idp56626352

Performance of MySQL "AS" and not "AS"

3 Answers3