SELECT UNION from multiple tables with same schema in MySQL

Question

I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use:

SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2013 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2014 UNION 
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2015 

I am quite certain there is a less redundant and verbose way to write this query. The schema for each of the table is exactly the same. This is in MySQL 5.6

• Anthony Genovese: Unless its select * from table, I don't think there is a better way.
• ypercubeᵀᴹ: One improvement you can probably make is using UNION ALL (I suppose there are no duplicate rows in these tables). It's probably going to improve performance a lot.
• hanxue: @agenovese you mean SELECT * FROM visitor_archive_2012 UNION SELECT * FROM visitor_archive_2013 UNION SELECT * FROM visitor_archive_2014 ?
• Michael Green: NB difference between UNION and UNION ALL and the likely performance implications.

Answer 1

Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job.

But, that may not even be wise. What queries benefit from your splitting it into multiple tables?

• hanxue: No good reason to split into multiple tables; it is a legacy application with the database designed by an inexperienced programmer

Answer 2

As @Rick says, I would question first why you need to have separate tables but assuming this is out of your control, if this is a regular query, may be simplest to create a view. Possibly if these are MyISAM tables you could consider a Merge table as well.