Very hard optimization problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mysql_query
Forum Newbie
Posts: 1
Joined: Tue Feb 24, 2009 7:39 pm

Very hard optimization problem

Post by mysql_query »

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!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Very hard optimization problem

Post by Benjamin »

Have a look at Materialized views: http://www.shinguz.ch/MySQL/mysql_mv.html
Post Reply