Associated query problem-CodePudding

Three tables, Media (Media), Tags (tag), MediaTagRef table (middle)
Three tables number respectively is: Media, 5 w, Tags, 5 w, 80 w MediaTagRef
Now you need to do keyword filtering search for Media, Media. The name LIKE '% 1%' OR Tags LIKE
'% 1%'

 
SELECT 
Count (*) 
The FROM 
(
SELECT 
Media. Id, Media. The name 
The FROM 
Mc_media Media 
LEFT the JOIN Mc_media_tag_ref TagRef ON TagRef. Object_id=Media. Id 
LEFT the JOIN Mc_tags Tags ON Tags. Id=TagRef. Tag_id 
WHERE Tags. The name LIKE '% 1%' OR Media. The name LIKE '% 1%' 
20 # LIMIT 
C)

Results: 651455
The affected line: 0
Time: 7.113 s

Returns the result need more than 7 seconds, it's can not accept, is there to write wrong?

Even if like to also want to 7 seconds,

CodePudding user response:

 SELECT 
Media. The id of the 
Media. The NAME 
The FROM 
Mc_media Media 
LEFT the JOIN Mc_media_tag_ref TagRef ON TagRef. Object_id=Media. Id 
LEFT the JOIN Mc_tags Tags ON Tags. Id=TagRef. Tag_id 
WHERE 
Tags. The NAME LIKE '% 1%' 
The OR Media. The NAME LIKE '% 1%' 
LIMIT of 20

This query within 100 ms, but unable to know the total number, can't do paging,
Also please help to see see!!

CodePudding user response:

From the point of the where condition, both like % 1%, fully using the index of less than, poor performance will of course, also need to see how filterability indexes, the filterability again within 10% agree that can effectively use the index.

CodePudding user response:

refer to the second floor rabitsky response:

from the point of the where condition, both like % 1%, completely can't use index, poor performance will of course, also need to see how filterability indexes, the filterability again within 10% agree that can effectively use the index.

Even if LIKE take out more than 7 seconds

CodePudding user response:

The execution plan Chou Chou

CodePudding user response:

refer to 4th floor to learning turn _ response:

see execution plan Chou Chou

Figure 2 is?

CodePudding user response:

And I have an idea, that is, simply add a tags to the Media field save Media, multiple use ', 'space, query time need not associated with so many tables,
Don't know that what are the disadvantages?