Thread: Slow query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0

    Slow query


    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_f ON vv.video_id = vht_f.video_id

    WHERE vv.active = 1
    AND( vht_f.tag_tag_id = #{pagination.tags[0]} OR vht_f.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.
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,575
    Rep Power
    1906
    what is the value of "too long"? how many rows do you have?

    You can try to use EXPLAIN SELECT to get an idea on how MySQL execute the query.
  4. #3
  5. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Since you don't actually show us the value for
    #{pagination.searchTermModified}

    I'll ask do you have the wild card % at the beginning of your search term? If so your query will have to run a table scan.

    Have you had a look at FULL TEXT INDEXES? that might be more appropriate for your search.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    search term modified is

    '%search_term%'

    video has 9999 records and tag has 4387.

    I am trying to avoid full text indexes if i can.
  8. #5
  9. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Originally Posted by dumkat
    search term modified is

    '%search_term%'
    So then you will always be faced with a table scan since no index can be used on a search beginning with the wild card.

    Originally Posted by dumkat
    I am trying to avoid full text indexes if i can.
    Why?
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    How would I rewrite the query?
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    also, the query really slows down when it is inner joining on the filter and that only uses tag_ids which are indexed.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    seems to me you are unnecessarily joining the same set of tables twice

    first this --

    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

    then this --

    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

    what's up with that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    5
    Rep Power
    0
    I am filtering the first query which is a search by the inner joining it with the second query which is a filter on tag which can be 0 to * tags.

    Although, I have changed the query for less joins in the original post.
  18. #10
  19. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    1. What does an EXPLAIN for the query look like?

    2. Do you have indexes on the joining columns?
    /Stefan

IMN logo majestic logo threadwatch logo seochat tools logo