Newbie SQL Question

harvillo

I have have 200,000 items (one table with 3 fields) that I need to query and was wondering if it's quicker to retrieve the data from one table or should I break the data up into say, 4 tables and then perform a JOIN of some sort (i.e. UNION) and then query for a result set. The reason for breaking the data into tables would be to break the data into sections for management reasons, not a must. Currently I'm using an ID in the table as a delineator between sections and it's working great. I'm worried what will happen down the line if I have a few million records.

Thanks

steadyguy

Of course, it's hard to give a well-rounded response without knowing what the table or what sort of data needs to be handled, but I see no reason why you should make your life harder with multiple tables - unless you have to, of course. As long as you have the table properly indexed, and optimize it from time to time, you should be fine - even with millions of records.

harvillo

I understand the question was very broad and I appreciate the response. I have all indexes set and optimize frequently.

Thanks

Sxooter

Any table that isn't very wide is usually pretty fast at <1,000,000 rows. So don't worry about breaking up the data unless you are gonna see some huge growth, and even then, there are other ways around this.

csn

I've used mysql tables with 100's of millions of rows and haven't experienced any problems really. However, if you're doing large INSERTS/UPDATES/DELETES they can take a while, depending on indexes.