Re: Table partitioning for maximum speed?

Jeff Boes wrote:
> Obviously, this is conceptually similar to what the index on the "md5" 
> column is supposed to do for us. However, partitioning moves just a 
> little of the processing load off the database server and onto the 
> machine running the application. That's important, because we can afford 
> more application machines as load increases, but we can't as easily 
> upgrade the database server.
> 
> Will a query against a table of 0.5 million rows beat a query against a 
> table of 7 million rows by a margin that makes it worth the hassle of 
> supporting 15 "extra" tables?
> 

I don't think 16 tables on the same server will help, but if you already 
have your app tier physically separate from the database tier, you could 
partition your data to more than one database server based on the first 
byte of the md5 column. I designed and built something similar a few 
years ago. We never got to the point where we really needed that kind of 
scalability, but it worked pretty well in (limited) testing.

Joe