how can i query a table that got split to 2 smaller tables? Union? view ?

Question

hello friends,

I have a very big table (nearly 2,000,000 records) that got split to 2 smaller tables. one table contains only records from last week and the other contains all the rest (which is a lot...)

now i got some Stored Procedures / Functions that used to query the big table before it got split.

i still need them to query the union of both tables, however it seems that creating a View which uses the union statement between the two tables lasts forever...

that's my view:

CREATE VIEW `united_tables_view` AS select * from table1 union select * from table2;

and then i'd like to switch everywhere the Stored procedure select from 'oldBigTable' to select from 'united_tables_view'...

i've tried adding indexes to make the time shorter but nothing helps... any Ideas?

PS

the view and union are my idea but any other creative idea would be perfect!

bring it on!

thanks!

Answer 1

If there is a reason not to, you should merge the tables rather than constantly query both of them. Here is question on StackOverflow about doing that:

http://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables

If you need to keep them seperate, you can use syntax along the lines of:

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

Here is an article about when to use SELECT, JOIN and UNION http://articles.techrepublic.com.com/5100-10878_11-1050307.html