MySQL Forums
Forum List  »  Merge Storage Engine

data warehouse
Posted by: bartosz.slowinski
Date: February 24, 2006 06:46AM

I want to have a very large table in mysql, over 1 000 000 000 of records.

It is not easy to have a table this big, so I want to split the data in daily tables and create a view to merge them (or use the merge engine).


CREATE VIEW BIGTABLE (date, col1, col2, col3...) AS
SELECT 200509130800, col1, col2, col3... FROM table200509130800
UNION ALL
SELECT 200509131000, col1, col2, col3... FROM table200509131000
UNION ALL
SELECT 200509131200, col1, col2, col3... FROM table200509131200
...etc...

but I need the optimizer to eliminate some of the small tables from the query when you run queries which include a condition, e.g.

select count(*) from bigtable where date >= 200509131000


is it possible in mysql?

also documentation states that using UNION ALL in VIEW creates a temporary table (creating a temporary table with 1 000 000 000 rows is not what I want - it will take very long)


I know there is a partitioning engine in 5.1 but it's not ready yet, is there a way to get around it in mysql 5.0?

Options: ReplyQuote


Subject
Views
Written By
Posted
data warehouse
5578
February 24, 2006 06:46AM
3876
February 24, 2006 08:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.