I have tried using InnoDB instead of MyISAM, though this has created new problems- the database is really slow and the row count changes every time.
In short I would rather stick with the devil I know for now. I have managed to improve performance a bit by adding another field to store a search variable result rather then doing the search every time... however I still have performance issues once I try to speed things up by having more then 10-12 parallel processes accessing the database (not all at the time time, but close enough together). I think the next step is to get rid of the ORDER BY rand(), however I need a reliable way of 'locking' that row, so another process doesn't try to do it as well thus wasting resources. I am doing this by issuing an UPDATE to a field after the SELECT, so the next process would exclude this result by virtue of it's WHERE clause, however I was finding that due to MySQL queuing requests or something, that sometimes a new SELECT request was coming in the time between the SELECT and UPDATE commands from the other process, so I would still end up with process duplication, and this was why I used the ORDER BY rand(), so the processes wouldn't all pile up in the same data base area, however this rand() part really sucks the juice after a certain point. This is the code in a nutshell - --- Start loop --- 1. SELECT job-list-variables WHERE x,y,z LIMIT 1 ORDER BY rand() 2. UPDATE job-list SET processing=1 WHERE id=id 3. PROCESS TASK (Time several seconds to a few minutes) 4. UPDATE job-list SET processing=done WHERE id=id --- Go back to start of loop --- If I can somehow stop other processes coming in between 1 and 2, I could remove the ORDER BY rand() part... Michael --~--~---------~--~----~------------~-------~--~----~ NZ PHP Users Group: http://groups.google.com/group/nzphpug To post, send email to [email protected] To unsubscribe, send email to [email protected] -~----------~----~----~----~------~----~------~--~---
