select with order by
Posted by:
John Wythe
Date: April 21, 2016 12:30PM
Hi All
let's say I have a select statement on a table with 4 fields, A,B,C, and D
I have an index on the table using fields A,B, and C in that order.
My select statement is select * from table where A >= 'A' and A <= 'K' and B >= "C" and B <= 'P' and C >= 'H' and C <= 'Z' order by B,D,C
Syntax may not be exactly correct, but hope you get the idea.
There is no index for fields B,D,C.
How does MySQL handle this, and will it lock the table during the sort sequence to get the order by, and if so why.
We are experiencing issues with table locks certainly when the index for A,B,C doesn't exist, but not sure about the case when it does.
Is there anyway around these table locks in either case (index , or no index).
The length of the table lock on average is 30 seconds to 30 mins when the table contains blobs. Note: our select * for tables that have blobs is all fields, except blob fields where we specify length(blob), or octet_length(blob).
John