CODEWITHSUNDEEP
mysqlindexing
Shaun
Shaun

Reputation: 2181

Do Mysql sub queries use indexes too?

I have a table that I do fulltext searching on. It's starting to get big already with a relatively small amount of users - 20 million rows

Searches will only ever need to be on rows that belong to the PKs relevant to the search ie rows that belong to that user, and at most, that's about 200 000 per user. I figured if the fulltext search was only done on a subquery that first selects that user's rows, it should be super fast eg

SELECT * FROM 
(SELECT * FROM table1 WHERE userID = 2 ) AS r 
WHERE MATCH (r.fullTextCol1) AGAINST ('+monkey* ' IN BOOLEAN MODE)
ORDER BY r.fullTextCol1, r.fullTextCol2 ASC LIMIT 0,50

However, this query takes 4 seconds.

EXPLAIN says...

1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    185927  Using where; Using filesort
2   DERIVED     table1      ref     PRIMARY,unique  unique  4       193082

My indexes are:

PRIMARY (userID, userSubList, userItemID)
FULLTEXT fullTextCol1
FULLTEXT fullTextCol2

The subquery seems to not use the userID index at all.

Is my thinking right in approaching it like this - sub selecting the relevent user row to search on?

Thanks for your time and help.

Upvotes: 0

Views: 74

Answers (1)

Jason Heo
Jason Heo

Reputation: 10246

Have you tried like this? :

SELECT *
FROM table1
WHERE userID = 2
  AND MATCH (fullTextCol1) AGAINST ('+monkey* ' IN BOOLEAN MODE)
ORDER BY fullTextCol1, fullTextCol2 ASC LIMIT 0,50;

Or run without ORDER BY to check JOIN is slow or ORDERing is slow (or mixed)

EDIT

In your case, composite index on (userID, fullTextCol1) is needed but MySQL doesn't have it. Another already answered about this. see Compound FULLTEXT index in MySQL

please, let me know above answer makes sense and it's result.

Upvotes: 1

Related Questions