Very hard optimization problem
Posted: Tue Feb 24, 2009 7:45 pm
Hi everyone,
I'm stumped on an optimization problem and would welcome your input:
I have a table with about 10 million rows. The web server is sending the database several requests per second that require a random selection of 20 rows from this table (not just a simple random 20 rows, there are certain constraints on matching fields, etc.). Obviously I need to do this as efficiently as possible, so I can't use a simple ORDER BY rand() select statement. I've read articles about selecting a random row_id on the application end but that won't work given the constraints of the query. My only other consideration is a caching solution where there's a cronjob periodically making expensive queries and caching the results in memcached for the web server to pull after each request, but I was looking for a database solution. Any help is greatly appreciated.
Thanks!
I'm stumped on an optimization problem and would welcome your input:
I have a table with about 10 million rows. The web server is sending the database several requests per second that require a random selection of 20 rows from this table (not just a simple random 20 rows, there are certain constraints on matching fields, etc.). Obviously I need to do this as efficiently as possible, so I can't use a simple ORDER BY rand() select statement. I've read articles about selecting a random row_id on the application end but that won't work given the constraints of the query. My only other consideration is a caching solution where there's a cronjob periodically making expensive queries and caching the results in memcached for the web server to pull after each request, but I was looking for a database solution. Any help is greatly appreciated.
Thanks!