SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Hybrid View

  1. #1
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Odd query profiler results

    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. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,070
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    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)
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    May 2008
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, I still couldn't find an underlying problem with the query, but changing my SELECT to a SELECT STRAIGHT_JOIN solved the problem.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •