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:
Thank you Scott for the URL :)
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
Post a Comment