Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How does table alias names affect performance?

Tags:

While reading about tuning SQL queries, I read somewhere that 'Always use table alias and prefix all column names with the aliases when you are using more than one table.'

How does table alias names affect performance? Or Do they actually affect?

like image 456
Bhushan Avatar asked Dec 02 '11 21:12

Bhushan


People also ask

What is difference between table aliases and column aliases do they affect performance?

Question: What is Difference between Table Aliases and Column Aliases? Do they Affect Performance? Answer: Table aliases are used to avoid any ambiguity with respect to columns selected in select statement which migh belong to two or more tables joined.

What is the purpose of a table alias?

The use of table aliases is to rename a table in a specific SQL statement. The renaming is a temporary change and the actual table name does not change in the database. The column aliases are used to rename a table's columns for the purpose of a particular SQL query.

What is the benefit of using an alias in SQL?

Alias refers to the practice of using a different temporary name (usually a short name or a name with a logical meaning) to a database table or a column in a table. The main advantage of using an alias is to help make the SQL statement more concise and readable.

Why are alias used for table and columns?

Aliases are created to make table or column names more readable. The renaming is just a temporary change and table name does not change in the original database. Aliases are useful when table or column names are big or not very readable.


1 Answers

The alias doesn't affect performance in any practical or measurable way at all (italics added on edit). That is, it would add a barely (if it all) measurable delay to query compilation. Once compiled (and re-used), it has no effect.

An alias removes ambiguity when you have more than one table because you know which table it comes from. It also prevents future table changes from breaking a query. Say, you add an audit column to one table where it already exists in another table. Queries without aliases using both tables will break.

An alias is also mandatory in some cases e.g. schema bound views.

The SQL parsing engine (that reads all queries before executing them, and uses the information to cache the compiled queries in the future for faster execution) is the only thing that looks at the aliases, and uses it to help remove ambiguities in symbol lookups. The system would already produce symbols, just like any other compilable statement in any other language, when it's being parsed prior to execution-storage.

like image 189
gbn Avatar answered Nov 15 '22 23:11

gbn