Worldcubeassociation.org: Can't search for Phil Yu!

[jgouly]

I'm sure there are other names that don't search well, but this is the one I found.

https://www.worldcubeassociation.org/persons?page=1&region=all&search=phil+yu

Searching for the part of his ID doesn't work either:
https://www.worldcubeassociation.org/persons?page=1&region=all&search=yuphi

His profile is https://www.worldcubeassociation.org/persons/2010YUPH01

[jonatanklosko]

@jfly It probably has to do with #2196.

[larspetrus]

You _can_ search for the start of his ID: https://www.worldcubeassociation.org/persons?page=1&region=all&search=2010yup

[jgouly]

Good point, I actually meant 'part'. I edited my comment to change that.

[larspetrus]

I didn't mean to nitpick, just to inform :)

One of the changes in #2196 was that you can only search WCA IDs from the beginning, not for arbitrary substrings. This is a big performance win, but does have this usability loss.

I'll leave it to @jfly to investigate, but I'd speculate that the MYSQL full text search for names does something weird with 1-2 character strings.

[jfly]

Yeah, mysql's fulltext search defaults to a 4 letter minimum on words. I just checked our mysql configuration:

mysql> show variables like 'ft_min%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 4     |
+-----------------+-------+
1 row in set (0.01 sec)

There's some information on changing this setting for AWS RDS here https://forums.aws.amazon.com/thread.jspa?threadID=92917 and here http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html, but I'm not sure this is a rabbit hole we want to go down.

A completely different approach might be to look into AWS's managed elasticsearch solution.

[larspetrus]

Lowering the MySQL limit is the better solution, but I'm not volunteering to figure out the RDS setting magic.

If you think that's a rabbit hole, Elastic Search is a rabbit Moria.

[larspetrus]

Because I only just realized it myself:

The "Phil Yu" search is failing because "Yu" simply isn't in the fulltext index. As far as the fulltext index is concerned his only name is "Phil".

[jgouly]

@jfly does the fulltext index always have to be used? Or can it be turned off for a particular query?

[jfly]

We introduced the fulltext index as a defense against some DOS attacks we were seeing a week ago, so turning off the fulltext index would open us up to those attacks again.

Someone just needs to take the time to dig into AWS RDS's settings.

[jgouly]

Ah, so turning it off for small searches wouldn't be a good idea. But for curiosity's sake, do you know if it's possible?

[jfly]

Yes, it's possible. As mentioned several times in this thread, take a look at #2196 if you want to see how we introduced the fulltext index.

[timreyn]

I noticed this today searching for Yusheng Du (new world record holder, so I'm guessing there will be a few searches for him in the near future).

If the protection against DOS attacks is still necessary, could we drop words less than four characters from the search query? I think it would be better to get the search results for all Yushengs than no results for this query.

[UnsolvedCypher]

I can't seem to reproduce the issue for either Yusheng Du or Phil Yu - is this still a problem?

[timreyn]

@UnsolvedCypher hmm, I can still reproduce: https://www.worldcubeassociation.org/persons?search=yusheng+du&page=1&region=all

[UnsolvedCypher]

@timreyn You're right- I didn't follow the above links and thought this was about search in the search bar, not the persons page search. Thanks!

[jfly]

Documenting how I changed this setting over on RDS.

1. I created a new "Parameter group" called default-mysql5-6-two-char-fulltext-min-word-len. From https://us-west-2.console.aws.amazon.com/rds/home?region=us-west-2#parameter-groups-detail:ids=default-mysql5-6-two-char-fulltext-min-word-len;type=DbParameterGroup;editing=false, you can see that I set ft_min_word_len and innodb_ft_min_token_size to 2:
   

2. I modified our RDS instance to apply this new parameter group immediately:
   

3. I rebooted our RDS instance, as per the instructions on https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html:
   

4. I then forced rebuilding of the fulltext index by droping the existing one and recreating it:

   ALTER TABLE Persons DROP INDEX index_Persons_on_name;
   ALTER TABLE Persons ADD FULLTEXT(name);
   

[jfly]

I think we can finally mark this issue closed =)