Announcement

Announcement Module
Collapse
No announcement yet.

Query is slow as soon as I use one join

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query is slow as soon as I use one join

    Guys,

    I hope you can help me out.. I'm a little shocked by the speed of my query..

    I hope I'm being clear in stating my problem and providing enough information.. I'll be really happy with any pointers to where the problem might be ..

    When I do a query without joins, for example:


    SELECT t1.id, t1.status, t1.filetype, t1.filename, t1.hits, t1.message, t1.title, t1.tags, t1.extension, t1.location, t1.uid, t1.upload, t1.moderationstatus FROM data_mediagallery AS t1 WHERE (t1.status = 3) AND (t1.vhost = 1) GROUP BY t1.id ORDER BY upload DESCLIMIT 50;


    The query takes about 0.17s on a table with 50.000 records.. Which is fine..

    When I do a a little more complex query, however.. it becomes slow..


    SELECT t1.id, t1.status, t1.filetype, t1.filename, t1.hits, t1.message, t1.title, t1.tags, t1.extension, t1.location, t1.uid, t1.upload, t1.moderationstatus, AVG(if(t2.vote is not null,t2.vote,0)) AS rating, COUNT(t3.time) AS commentcount, t4.user AS user_nameFROM data_mediagallery AS t1 LEFT JOIN data_mediagalleryvotes AS t2 ON t1.id = t2.mid LEFT JOIN data_mediagallerycomments AS t3 ON t1.id = t3.mid LEFT JOIN users AS t4 ON t1.uid = t4.id WHERE (t1.status = 3) AND (t1.vhost = 1) GROUP BY t1.id ORDER BY commentcount DESC LIMIT 50;



    Ok, so it is understandable this is a bit slower (more than 2 seconds..) but, if I take any 2 out of 3 joins the difference is minimal..

    So what I conclude (but I'm not sure) every time I'm using a join, a different method to lookup the data from the main table is being used.. Because it doesn't matter which join I use, or how many joins with other tables.. the speed difference is about the same..

    However if I do an explain, this is what I get:

    *************************** 1. row ***************************
    id: 1
    select_type: SIMPLE
    table: t1
    type: ref
    possible_keys: vhost_status
    key: vhost_status
    key_len: 10
    ref: const,const
    rows: 26206
    Extra: Using where; Using temporary; Using filesort
    *************************** 2. row ***************************
    id: 1
    select_type: SIMPLE
    table: t2
    type: ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: sabretooth2.t1.id
    rows: 1
    Extra:
    *************************** 3. row ***************************
    id: 1
    select_type: SIMPLE
    table: t3
    type: ref
    possible_keys: mid
    key: mid
    key_len: 5
    ref: sabretooth2.t1.id
    rows: 1
    Extra:
    *************************** 4. row ***************************
    id: 1
    select_type: SIMPLE
    table: t4
    type: eq_ref
    possible_keys: PRIMARY
    key: PRIMARY
    key_len: 4
    ref: sabretooth2.t1.uid
    rows: 1
    Extra:


    The exact same keys and lookup method are being used from my first table as when I do the query without any joins.. so that can't be it..

    Then I figured.. maybe it has to do with my LIMIT.. In the case when there's a JOIN done, in fact all the rows of the data_mediagallery table are being loaded..


    I really hope somebody can help me out.. this app is close to going live and could cause a meltdown

    If I can provide more information.. I'll be happy to give anything that might be needed.. Any pointers would be great!

  • #2
    by the way.. I'm running 5.0.32

    Comment


    • #3
      You have a faulty GROUP BY syntax since you are selecting columns that are not part of the GROUP BY expression without using group functions.
      Which means that you can't be sure which of the values that you get.
      MySQL is basically the only DBMS in the world that allows it.

      As a suggestion it is also good if you supply the explain of the query that is fast so that we have something to compare with.

      But I'm guessing that the real reason why your joins are slow is probably that your original query are using a combined index on:
      (status, vhost, id)
      and that means that it can solve both the WHERE clause and the GROUP BY with the same index. Making that query very fast.

      But as soon as you join with one other table the GROUP BY can no longer be optimized that way so your DB needs to build a temporary table and sort that.

      My suggestion is that you increase your sort_buffer_size server variable.
      If the default 2Mb is to small MySQL will instead create a temporary file and sort on that which is _much_ slower than in memory sort.

      Comment

      Working...
      X