MySQL table question

[Anon]

I am currently converting some internal customer support software from text file databases to MySQL. Currently, I am storing call/event header information in one table and all comments (approximately 100,000 comments) in a second table. I have two questions I could really use some advice with.

Since the comment table is indexed by call # and comment #, would it be more efficient to combine those two fields into one single field (callnumber_commentnumber for example) or would it really make a difference speed wise at least on retrievals?

My second question is whether it is more efficient to store say 100,000 records in one single table or spread them over 10,000 tables? I am not sure if large numbers of tables causes problems for MySQL or negatively affects performance. Having 10,000 tables (one for each call/event) with 10 comments each (average) would mean much faster retrieval times.

Any advice anyone has on these two questions would be greatly appreciated.

twixxer

[brandonschnell]

keep the comments/calls id columns seperate or else its going to be a nightmare to update them later. make sure each id column has an index.

go with one table with 100,000 rows or else you'll go insane.

mysql will be fine with a table of that size if its properly indexed and the queries are reasonable.

consider a second table/server and archive old rows to the second table/server.

[Anon]

Thank you for the suggestions!

We are already using an archive system in our current file formats so the program logic won't have to be altered by implementing a separate archive table/server.

Keeping up with 10,000 tables would not be a problem since the call ID table would have all of the reference codes (it is basically an index to the comments records), but SEARCHING through 10,000 tables would be a major pain I'm sure.

I'm going to do what you suggested and stick with the larger comments table but move older calls/comments to an archive table to reduce search and retrieval times.

Thanks,
twixxer