#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    1
    Rep Power
    0

    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.

    Suggestions?
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    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.
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo