maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Hi again guys :)

i'm with a new problem

i have MANY (10000+) tables with same struct, and i want to execute a
select from "some" (100+) tables

i was thinking about creating a view with all tables, something like:

CREATE VIEW view_name AS
SELECT * FROM table1 WHERE primary_key=<a value only at table1>
UNION ALL
SELECT * FROM table2 WHERE primary_key=<a value only at table2>
UNION ALL
SELECT * FROM table3 WHERE primary_key=<a value only at table3>
UNION ALL
SELECT * FROM table4 WHERE primary_key=<a value only at table4>
UNION ALL
...
SELECT * FROM tablen WHERE primary_key=<a value only at tablen>


but i don't know if mysql optimizer will do a good job when i execute
something like:

SELECT * FROM view_name WHERE primary_key=<a value only located at table1>

and just execute the query at table1 instead of alllllllll 10000+ tables

i'm considering restruture database to a spider engine or any other method
if i don't have other option

any idea is well come

-- 
Roberto Spadim