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.

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.

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.

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.

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.



Why?

How would I rewrite the query?

also, the query really slows down when it is inner joining on the filter and that only uses tag_ids which are indexed.

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?

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.

1. What does an EXPLAIN for the query look like?

2. Do you have indexes on the joining columns?