Re: Counting execution time by using stored procedure

[Rick James]

Hey, I had not thought of that UUID kludge to get a precise time. Of course, since 5.6.4, NOW(6) is an easier way.

Why so slow? Are you running on Windows? What version of MySQL? Which ENGINE? What is the value of innodb_file_per_table? Well, I don't have to have the answers...

A table involves one to three files, depending on the answers to the questions above. It takes OS time to open-write-close those file(s). These files live in a directory named after the database, so the directory needs to be update 1-3 times. Furthermore, if the tables already existed, then the DROP deleted the 1-3 files and removed 1-3 entries from the directory.

CREATE TABLE is not an exciting action to be timing. The typical mysql system does a million times as many SELECTs and it does CREATE TABLEs.

Timing SELECTs is much more challenging. A simple SELECT, with everything properly cached, will take a millisecond or less. A complex SELECT could take days. Or even years. Occasionally someone asks this forum why a query "won't finish"; we calculate that it should take centuries to finish.

While you are in the learning phase, please not that the "5" means nothing in INT(5). An INT is a 32-bit value.

If you are going to ask why 3743209 (microseconds) and 00:00:03.734375 are not the same... Well, the UUID() and NOW() were not called at the same time.