Do Mysql sub queries use indexes too?

Question

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.

score 1 · Answer 1 · edited May 23 '17 at 12:05

1

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.

edited May 23 '17 at 12:05

Community

1
1

answered Nov 16 '13 at 05:39

Jason Heo

9,956
2
36
64

Thanks Jungsu. It's quicker, but still 2.5 secs, and the ORDER BY about the same - the index in explain is: 1 SIMPLE table1 fulltext PRIMARY,fullTextCol1 fullTextCol1 0 1 Using where; Using filesort - is there not a way to tell MySQL "only do the MATCH on userIDs that are 2?" It feels like it's searching the WHOLE table for monkey, then only returning results for userID=2 – Shaun Nov 16 '13 at 08:26
@Shaun you're right. generally speaking, `COMPOSITE INDEX` can search more than 2 columns. But it seems to be that `FULLTEXT` can't be member of composite index. I'll find some better way for you. – Jason Heo Nov 16 '13 at 09:44
OK - so in my case, where I have two fulltext columns, I'd set up a third that contains the user's id along with static characters that are NOT allowed in the two columns eg I know that ? and > are filtered from those two columns, so something like *>2333 for user 2333 in the third column (called compFT). Then, for searches on fullTextCol1, I'd need a fulltext index over fullTextCol1 and compFT, and just include *>2333 in the search along with whatever the keywords are, right? – Shaun Nov 19 '13 at 07:20
@Shaun yes. I think so. but I'm not sure tat special character like `*>` will not be stored in FTS. And `>` has special meaning (http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html). As http://stackoverflow.com/questions/18949633/compound-fulltext-index-in-mysql says, `userid_3333` would be better. – Jason Heo Nov 19 '13 at 07:30
Cheers for your help Jungsu – Shaun Nov 24 '13 at 12:17

Do Mysql sub queries use indexes too?

1 Answers1