04-11-2011, 03:41 PM
So while backing up some databases today, I noticed that two of my tables had reached fairly substantial sizes: half a million records. Fortunately, I also know only about 50k of those records are in use at any given time, ie, the rest are basically archived. I guess, never having had a very large DB, is this problematic? When should I start considering a DB large, or too large? I know indexing can be an issue with large DBs, but if file space is not a concern, are there other major issues I should be aware of?
04-11-2011, 07:54 PM
Largest tables in MYSQL that I have *personally* dealt with were 3GB each. The records were quite small, and even with indexes each only ate up about 150 bytes. So 3GB divided by 150 is 20,000,000 records. And it performed just fine. (It was used in *REAL TIME* for both storage and retrieval.)
SO... I don't think MySQL is going to choke on half a million records. But the size of the records *does* matter some. Could you consider splitting that table into active and archival records? (Notice I didn't ask "should you".) If it's a lot of trouble, don't bother. If it isn't, you could try it to see if it helped performance. But don't be too surprised if the performance boost is only nominal.
04-12-2011, 03:01 AM
The only issue i've had with large tables was when doing a query that didn't properly use an index. In such as case your query has to check each row. The performance will suffer with each new row added.
If your indices work as expected you won't have this issue.
04-18-2011, 07:08 AM
The other issue is if the index is complicated or too big to fit in memory, then you might have some performance gains from tweaking your database settings, redefining your indexes, or reducing the size of your table.
04-18-2011, 06:40 PM
MySQL doesn't do *too* badly when an index doesn't fit in memory, *IF* you are requesting records that are relatively close together by index order. But, yes, you get much better performance if the index fits in memory *AND* if the machine's memory is not in use by many other processes. It doesn't do much good to fit an index in memory only to have that memory swapped out because of pressure from other processes. Having a dedicated (or near-dedicated) server for the DB may be the best solution is you have really large indexes.