Optimizing SQL query

Hi,

I'd like to optimize this query:

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

Code (markup):

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 ?

 

You ould try something like this:

LOCK TABLES foo READ;
SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo;
SELECT * FROM foo LIMIT $rand_row, 1;
UNLOCK TABLES;

Expat

 

SQL Improvements:

only use "*" if you need all columns.

A combined index on active AND mainweight would increase the speed of the WHERE.

 

Test this statement in mysql (the utility) and see how long it takes. I ran a quick test and a query similar to yours returns 5 rows out of 10K in under 0.08 seconds on a regular mid-range machine. If the time you get in mysql is much less than 0.8 seconds, then most likely it's your server-side script that does something it isn't supposed to.

If mysql reports you something close to 0.8 sec, then your database server isn't setup correctly (e.g. if you are using desktop/development setup).

active is a boolean value and including it in any index won't do much good. Indexing mainweight isn't going to help either if its value ranges from 1 to 10 or so. Besides, this particular select statement most likely won't benefit from this index anyway because the condition is mainweight != 0, so the server would still have to sort the same result set (considering that there is no record with mainweight == 0).

J.D.