September 12th, 2012, 10:09 PM
Find the gaps query optimization
I have a query that is working well but takes on average 5.2-6 seconds to run
SELECT CAST(a.biddernumber AS SIGNED) + 1 AS bn
FROM bidders AS a
WHERE AND NOT EXISTS
(SELECT b.biddernumber FROM bidders AS b
WHERE CAST(a.biddernumber AS SIGNED) + 1 = b.biddernumber)
ORDER BY CAST(bn AS SIGNED) LIMIT 0, 1
I'm trying to find the first missing "biddernumber" in a varchar field. I've tried converting to INT and the speed isn't improved. The speed is dramatically improved if I convert to INT and make that field an index but I do a lot of Inserts to the table so I'm not sure that would be wise.
September 14th, 2012, 10:25 AM
That query will, depending on volume, always be slow without an index. And all the AS SIGNED conversions is not helping either.
But a more important question:
Is there some particular reason why you are playing fill-in-the-blanks?
Make the biddernumber an INT primary key auto_increment and let the database give you a new biddernumber every time you create a new bidder solves the entire issue.