CODEWITHSUNDEEP
mysql
priyanka patel
priyanka patel

Reputation: 637

retrieve data from multiple table with same fields in mysql

I have table "ABC_history" which store history having millions of records,
so i am thinking to create month and year wise table like 

ABC_history_January_2012
ABC_history_February_2012 
ABC_history_march_2012
.
.
.
ABC_history_December_2012

i want to fetch data using condition date is between "2012-01-15" and "2012-02-15", how can i achieve this using single query or is there any other way to store large data?

want to implement same concept of inheritance in postgreSQL. refer for example Best way to store huge log data

Upvotes: 0

Views: 538

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269723

You have a poor data structure. Unfortunately, the only way to do a generic query is to union all the tables together:

select *
from ((select * from ABC_history_January_2012) union all
      (select * from ABC_history_February_2012) union all 
      (select * from ABC_history_march_2012) union all
      . . .
      (select * from ABC_history_December_2012)
     ) abch
where date is between '2012-01-15' and '2012-02-15';

Very expensive query.

What you want is table partitioning. This allows you to store one table in multiple "parts". Each part would be determined by a date range. Then, any query that uses the date would only read the data that is needed. You can read more about table partitioning here.

Upvotes: 3

Related Questions