.I have too many tables X1,X2,…Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this
desc X1; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | ReqF | int(11) | Yes | | NULL | | | EmpName | varchar(32) | Yes | MUL | NULL | |
now I am supposed to return all ReqF from departments that John works in.
I know i can do so by left join like this
select X1.ReqF,X2.ReqF,...,Xn.ReqF FROM X1 left join X2 on (EmpName) left join .... left join Xn on X1.EmpName=Xn.EmpName where X1.EmpName='John'
is there any better way to do so and what if we need to retrieve different fields from tables (ex X1.ReqF1, X2.ReqF2 or random number of fields from tables like [SELECT X1.F1, X1.F2, X2.F3,…])??
Best Answer
If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.
You can create a single table that consumes no additional space except a
.frm
file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.Here is how you can do this:
Using this method, you can query the 4 tables at the same time like this:
Was that simple, or what ???
In your case, you have 75 tables. You would do this:
The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:
Give it a Try !!!