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

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?

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.