4 min 20 sec
So next time somebody complains about NDB taking a long time in CREATE TABLE, you’re welcome to point them to this :)
- A single CREATE TABLE statement
- It had ONE column
- It was an ENUM column.
- With 70,000 possible values.
- It was 605kb of SQL.
- It ran on Drizzle
This was to test if you could create an ENUM column with greater than 216 possible values (you’re not supposed to be able to) – bug 589031 has been filed.
How does it compare to MySQL? Well… there are other problems (Bug 54194 – ENUM limit of 65535 elements isn’t true filed). Since we don’t have any limitations in Drizzle due to the FRM file format, we actually get to execute the CREATE TABLE statement.
Still, why did this take four and a half minutes? I luckily managed to run poor man’s profiler during query execution. I very easily found out that I had this thread constantly running check_duplicates_in_interval(), which does a stupid linear search for duplicates. It turns out, that for 70,000 items, this takes approximately four minutes and 19.5 seconds. Bug 589055 CREATE TABLE with ENUM fields with large elements takes forever (where forever is defined as a bit over four minutes) filed.
So I replaced check_duplicates_in_interval() with a implementation using a hash table (boost::unordered_set actually) as I wasn’t quite immediately in the mood for ripping out all of TYPELIB from the server. I can now run the CREATE TABLE statement in less than half a second.
So now, I can run my test case in much less time and indeed check for correct behaviour rather quickly.
I do have an urge to find out how big I can get a valid table definition file to though…. should be over 32MB…