Thursday, January 19, 2006

Advice from the MySQL experts needed

On one of my databases, MySql is not using indexes when choosing a weighted-random row from a MySQL table.

For e.g. a query like this:


SELECT cid from category where [...] order by rand()*weight DESC LIMIT 1



is not using indices as shown by EXPLAIN statements. Obviously this overloads the server.

My question is that what can be done to select a weighted-random row from a MySQL table more efficiently? Has this been improved in MySQL5?

How can MySQL be forced to use indices for queries like this where we have weighted rand() function involved?

Any pointers are appreciated.

Frank

2 comments:

Frank said...

Thank you Scott for the URL :)

ArcangeloGabbriello said...

Hi maybe u can help me. I am really desperate, i tried to write all MySQL forums but notobdy answers. I have this problem:

While creating a trigger on MySQL 5.5.33 as i put an "IF STATEMENT" inside like this one:

CREATE TRIGGER status_history_upd AFTER UPDATE ON projects
FOR EACH ROW

IF NEW.project_status != OLD.project_status THEN
INSERT INTO project_status_history
SET status_change_date = NEW.project_edit_date,
project_id = NEW.project_id,
status_id = NEW.project_status;
END IF;

for some reason it gives me back this error:

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO project_status_history
SET status_change_date = NEW.project_edi' at line 5

I really have to put the "IF STATEMENT" because i need to run the INSERT just in some cases.
If I remove the "IF STATEMENT" it works fine.

Can you please help?

Thanks

Gabriele