Slow query

Discussion in 'MySQL' started by dumkat, Sep 6, 2012.

0
  1. #1
    This query is taking too long. I am doing an intersect. The main query searches on term that can in be in few tables related to video including tag. I am intersecting the search query with a filter query that is based on 0 to * tags.
    I am using an INNER JOIN on the search query and filter query.

    I need a faster query.

    Ignore the java ibatis injections.

    SELECT v.* FROM video v
    INNER JOIN channel_has_video chv ON v.video_id = chv.video_id
    INNER JOIN channel c ON chv.channel_id = c.channel_id
    LEFT JOIN video_has_tag vht ON v.video_id = vht.video_id
    LEFT JOIN tag t ON vht.tag_tag_id = t.tag_id
    LEFT JOIN video_has_artifact vha ON v.video_id = vha.video_id
    LEFT JOIN artifact art ON vha.artifact_id = art.artifact_id
    INNER JOIN
    (
    SELECT vv.* FROM video vv
    INNER JOIN channel_has_video chvv ON vv.video_id = chvv.video_id
    INNER JOIN channel cv ON chvv.channel_id = cv.channel_id
    INNER JOIN video_has_tag vht_0 ON vv.video_id = vht_0.video_id
    INNER JOIN video_has_tag vht_1 ON vv.video_id = vht_1.video_id
    WHERE vv.active = 1
    AND( vht_0.tag_tag_id = #{pagination.tags[0]} OR vht_1.tag_tag_id = #{pagination.tags[1]})
    ) st
    ON (st.video_id = v.video_id)
    WHERE v.active = 1
    AND c.active = 1
    AND c.search_enabled = 1
    AND ( v.title LIKE #{pagination.searchTermModified} OR v.description LIKE #{pagination.searchTermModified} OR t.tag_text LIKE #{pagination.searchTermModified} OR art.title LIKE #{pagination.searchTermModified}
    OR art.description LIKE #{pagination.searchTermModified} )
    GROUP BY v.video_id
    LIMIT #{pagination.limitStart}, #{pagination.limit}


    Thanks. Any help would be greatly appreciated.
     
    dumkat, Sep 6, 2012 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Your nested query in the first INNER JOIN is most likely what's killing it.

    Is there any way to accomplish it without the
    INNER JOIN (SELECT...
     
    jestep, Sep 10, 2012 IP
  3. quotemeremovals

    quotemeremovals Peon

    Messages:
    19
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Hi this is The slow query log consists of SQL statements that took more than are 1 and 10, respectively.
     
    quotemeremovals, Sep 12, 2012 IP
  4. dynamic1234

    dynamic1234 Peon

    Messages:
    38
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    In mysql maximum how many tables can be join using inner join. I have used maximum four tabels...
    Thank You
     
    dynamic1234, Sep 13, 2012 IP
  5. Sarah Reece

    Sarah Reece Greenhorn

    Messages:
    11
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    13
    #5
    There is no limit to the number of tables that can be used in an inner join. Only that you would need to be careful of performance and possibility of missing out on a join condition resulting in a cartesian product.
     
    Sarah Reece, Sep 17, 2012 IP