What's the largest MySQL table you have dealt with?

[RLW]

I have a MySQL table that has 17 million rows in it. It's the largest table I've ever worked with. Some of my queries are a little slow, and even a simple select with no joins can take 6+ seconds on the server (even longer on my local machine). With the query cache and what not, the major pages on my site load pretty quick and it's only the 'obscure' pages that are slow.

Since search engines look at page load speeds now, and search engines will look at the 'obscure' pages on your site, I guess it's more important than ever to make sure all of your pages load as quickly as possible. So I've been trying to optimise my queries, and I've got indexes on my tables, but I'm wondering whether I should be looking at other options as well (e.g. a better server.)

I'm by no means a professional when it come to MySQL or databases in general, so it got me wondering, what's the largest MySQL table you have used, and when (if ever) did you start to run into any problems?

 

[jmcc]

Look for a possible way of breaking down the data based on the most frequently used data. A good index will only go so far in solving a problem. However if a simple select (with a direct query instead of a wildcard (%) one) is taking six seconds, then there may be a problem with the choice of data used for the index. Some of the large tables in the main HosterStats database (the historical domain records) have between 25 and 33 million rows. The first query on the largest of those tables takes 0.04 Seconds. The server is just a P4 3.0G one with 4G of RAM. If you are serious about large database work, get the O'Reilly book "High Performance MySQL" as it is probably the single most useful book on large MySQL database work that's been published.

Regards...jmcc

 

[RLW]

Look for a possible way of breaking down the data based on the most frequently used data. A good index will only go so far in solving a problem. However if a simple select (with a direct query instead of a wildcard (%) one) is taking six seconds, then there may be a problem with the choice of data used for the index. Some of the large tables in the main HosterStats database (the historical domain records) have between 25 and 33 million rows. The first query on the largest of those tables takes 0.04 Seconds. The server is just a P4 3.0G one with 4G of RAM. If you are serious about large database work, get the O'Reilly book "High Performance MySQL" as it is probably the single most useful book on large MySQL database work that's been published.

Regards...jmcc

Thanks for the ideas. I found a few excerpts from that book and it looks good. I'll grab a copy in the next few weeks and have a read.

The table I'm working with was a bit of an experiment because the databases I'd worked with in the past were relatively small so I couldn't really see the impact my queries had... I'm looking forward to learning more and improving myskills.