> select * from work_packets where work_data is null and get_lock(id,0)=1
> limit 1;
Sorry, I should have said that my example was pseudo-code that I was
typing from memory. The query you've suggested is certainly an
improvement - I don't know why I though that the get_lock needed to be in
a subquery, but yours is certainly faster.
> My guess is your initial reason for ORDER BY RAND() was to minimize
> duplicate work, but if you're using locks it'll never happen, so you might
> as well just grab the first available packet. That query shouldn't even
> take 2 seconds, unless you have 50k rows in the table and no index on
Your guess is correct. I've re-written the query more or less as you've
suggested and it now takes 4 seconds to complete, which I think is
There is not an index on the work_data column as it's a longblob and I was
under the impression that indexing a longblob field wasn't helpful. Maybe
I should add a work_data_size field as an integer, index that, and search
for records where work_data_size = 0.
For what it's worth, the table currently has 800,000 rows in it and is
likely to expand in the future.