Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

.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,...])??

share|improve this question
    
No, not with that design. Well you could do a Union instead of Join but you'll still have to reference all tables. –  idstam Jan 4 '12 at 8:51
add comment

2 Answers

up vote 4 down vote accepted

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:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

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:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

share|improve this answer
add comment

Given this table structure I'd do:

      SELECT ReqF FROM X1 WHERE EmpName='John'
UNION SELECT ReqF FROM X2 WHERE EmpName='John'
UNION SELECT ReqF FROM X3 WHERE EmpName='John'
UNION SELECT ReqF FROM X4 WHERE EmpName='John'
...
UNION SELECT ReqF FROM Xn WHERE EmpName='John'

because that would bring the ReqF values in a more consistent structure (same column).

share|improve this answer
    
Always use UNION ALL or use UNION DISTINCT when you consciously want de-duplication. It's faster, but more importantly it prevents accidentally losing rows. –  Jannes Jan 4 '12 at 17:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.