Mysql Query is slow with one where condition on Exists-CodePudding

I got 3 tables and the relations between them is many to many .

This is the Image of my tables with its columns

I need to get the hashtag names that has files with specific category_id .

the problem is that when i use the below query without specifying the category , it performs well like 0.05s .

select `hashtags`.`slug` 
from `hashtags` 
where EXISTS (
   select * from `files` 
   inner join `file_hashtags` on `files`.`id` = `file_hashtags`.`file_id` 
   where `hashtags`.`id` = `file_hashtags`.`hashtag_id` 
);

but when i perform the below query with specified category it goes like 3s to perform .

select `hashtags`.`slug` 
from `hashtags` 
where EXISTS (
   select * from `files` 
   inner join `file_hashtags` on `files`.`id` = `file_hashtags`.`file_id` 
   where `hashtags`.`id` = `file_hashtags`.`hashtag_id` 
   and `files`.`category_id`=2 
);

what can i do to improve this to get the better query time ? also i did this query using IN instead of Exists , but the result is the same with a little like 0.1s better perform in time.

about the indexes :

files table has ID as primary key , and category_id as BTREE index (need this for when i need to perform easy queries like get files with specific category) , and slug as Unique index .
hashtags table has ID as primary key , and slug as Unique index .
file_hashtags table has two foregin keys for their tables , also (file_id , hashtag_id) is Primary.

also there are about 150k rows in files table , 75 in hashtags table and 260k in the pivot table.

CodePudding user response：

You can use inner join without exist:

select distinct `hashtags`.`slug` 
from `hashtags`
inner join `file_hashtags` on `hashtags`.`id` = `file_hashtags`.`hashtag_id`
inner join `files` on `files`.`id` = `file_hashtags`.`file_id`
where `files`.`category_id`=2
group by `hashtags`.`slug`;

CodePudding user response：

Try like this.

SELECT hashtags.slug
FROM hashtags
WHERE EXISTS
(SELECT * FROM (SELECT * 
FROM files
WHERE category_id = 2) A
INNER JOIN file_hashtags ON A.id = file_hashtags.file_id
WHERE hashtags.id = file_hashtags.hashtag_id)

If the number of rows in the files table is large, the number of join records can be reduced by performing a filter before proceeding with the join operation.