Re: Are queries queued?

[Rick James]

LOCK will prevent SELECTs in MyISAM -- after all the LOCK implies that something is about to change.

Don't mix LOCK and BEGIN/COMMIT in InnoDB.

I'm assuming you have some programming language that will let you get the 3 rows selected and build the IN clause needed.

You have 4000 rows, and no useful indexes. It takes time to scan the table. Sometimes it will find the 3 rows quickly, sometimes it will have to scan all 4000 and maybe find none.

Since everything can easily be kept in memory, my pessimistic 1000 in 10 seconds can be relaxed. It can probably handle more than that.

Surely there is more to the application? Will the user be shown the 3 items and do something with them? Will some of them then be changed back to 'available'?

It is still possible that you will get errors on InnoDB and have to reattempt the SELECT+UPDATE.

You MUST test your application BEFORE the sale date.

Is it always "=" tests? Will the user always specify all the fields? If so, an index on (status, supplier_e, color_b, cost_type, id) would avoid scanning 4000 rows. Instead only 3 would need to be looked at.