Best thing to do it to try it but I suspect your hunch is correct.

Really, it would be a good idea to merge all your data into a single table. Perhaps use partitioning https://dev.mysql.com/doc/refman/5.5/en/partitioning.html which the optimizer should be able to take advantage of.

From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@lists.launchpad.net] On Behalf Of Roberto Spadim
Sent: Wednesday, September 23, 2015 4:06 AM
To: Maria Discuss
Subject: [Maria-discuss] doubt about merge table (10000+) with same structure

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