How to give table an alternative name in MySQL?-CodePudding

I want to use a table that would be accessible under two names (something like e-mail address alias) i.e. I want queries:

select * from my_table_name

and

select * from my_alt_table_name

return records from the same table.

I know I can use a view and then run query on view, but wouldn't it be less efficient?

CodePudding user response：

Creating a view would look like this

CREATE VIEW my_alt_table_name AS SELECT * FROM my_table_name;

and next time you can use it like this

SELECT * FROM my_alt_table_name ;

CodePudding user response：

You can't have a table with multiple names, so yeah, just use views. There will be no impact on performance during the runtime, however during the compilation of the query, there will be just a small delay time for the compilation of the view to be transformed into a table in memory which is absolutely negligible

CREATE VIEW table_name_alias AS SELECT * FROM table_name;

SELECT * FROM table_name_alias;

CodePudding user response：

SELECT * FROM `products` prod WHERE prod.category_id = 18

we can use table_name_alias

SELECT * FROM TableName TableAlias

CodePudding user response：

I know I can use a view and then run query on view, but wouldn't it be less efficient?

If the view is strictly SELECT * FROM table, without any additions (WHERE and so on), then there is no difference does you use the table or the view as an alias.

See small DEMO.

See SHOW WARNING outputs - they claims that the server is smart enough for to understand that it may/must use the table itself.

See EXPLAIN outputs - they claims that the server is smart enough for to understand that it may/must use the index which is present in the table structure.

expanded DEMO fiddle - analyse it by itself.

Also study CREATE VIEW Statement, ALGORITHM clause, and View Processing Algorithms. Try to add ALGORITHM = TEMPTABLE to DEMO and investigate the changes.