How can i Optimize sql Query ?

Hi,

I'd like to optimize this query:


Code:

SELECT *
FROM `links`
WHERE active = "1" AND mainweight != 0 ORDER BY Rand()*(1/mainweight) LIMIT
5

I have a database of links wich has 3 000 rows. I'd like to select weighted
random links from it (mainweight is the weight of the link). On my serwer
this query is executing 0.8 sec. When i have 50-100 users on my site this is
a problem.

How can i optimize/change this query ?

Some suggestions: Simple optimisations...

Make sure you have an index on column `active`

Either:
Add a new column and precalculate 1/mainweight so you don't have to
calculate it in the query
Or:
Simplify the computation in the query to Rand()/mainweight

Bigger changes...

Instead of querying the whole table for each request, create a CRON job
to create a smaller (~100 row) result table and query from that on each
request.

Your visitors will not know or care that they are seeing links from the
same (smaller) subset as all other visitors, and as long as you refresh
the subset periodically the result will be similar to querying the
whole table.

---
Steve