Odd query profiler results

Databases
1

These are the results of a query I’m profiling in MySQL. I’ve never had problems with or even noticed the “statistics” row in the past, could someone explain what this field is and why it could be so high?

Thanks.

±---------------------±----------+
| Status | Duration |
±---------------------±----------+
| (initialization) | 0.0000027 |
| Opening tables | 0.006038 |
| System lock | 0.0000072 |
| Table lock | 0.0000095 |
| init | 0.0000905 |
| optimizing | 0.000039 |
| statistics | 51.108223 |
| preparing | 0.0000975 |
| Creating tmp table | 0.0015625 |
| executing | 0.000003 |
| Copying to tmp table | 0.001369 |
| Sorting result | 0.0000155 |
| Sending data | 0.0002792 |
| end | 0.0000045 |
| removing tmp table | 0.0006055 |
| end | 0.000005 |
| query end | 0.0000052 |
| freeing items | 0.000025 |
| removing tmp table | 0.0000215 |
| freeing items | 0.0000072 |
| closing tables | 0.0000585 |
| logging slow query | 0.000003 |
±---------------------±----------+
22 rows in set (0.00 sec)

mysql>

2

I’m guessing the query uses some aggregate function like MIN(), MAX(), AVG(), STDEV() or something like that. MySQL has to calculate the results and calls this “statics”.

(just a guess by the way)

3

Thanks. Did some digging and finally found some info about it being related to the query planner calculating some statistics regarding efficiency of joins.

My query has a series of joins where one of the intermediate inner joins produced an empty set. When I changed this inner join to a left join, the problem went away. Its a mystery to me.

4

well, I still couldn’t find an underlying problem with the query, but changing my SELECT to a SELECT STRAIGHT_JOIN solved the problem.