2

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

4
  • Unless its select * from table, I don't think there is a better way. Jun 23, 2016 at 15:16
  • 5
    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. Jun 23, 2016 at 16:58
  • @agenovese you mean SELECT * FROM visitor_archive_2012 UNION SELECT * FROM visitor_archive_2013 UNION SELECT * FROM visitor_archive_2014 ?
    – hanxue
    Jun 24, 2016 at 3:47
  • NB difference between UNION and UNION ALL and the likely performance implications. Jul 3, 2016 at 16:45

2 Answers 2

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?

1
  • No good reason to split into multiple tables; it is a legacy application with the database designed by an inexperienced programmer
    – hanxue
    Aug 17, 2017 at 16:22
1

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.

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .