Massive speed improvements -- details within

[styx]

Hello,

Look at the one-line change I've done. It's way better. It also has a wildcard in it, for instance for the test * of it. Alternatively, it might be possible to remove the * and to have the user use wildcards. Maybe that's better.

As I said, disclaimers and stuff - it might break stuff. But it also shows potential. On an indexing site, search is bread and butter and this is a massive improvement - but only if it works correctly. I would feel so much better myself if a "real coder" could do this properly! So please people, have a look!

Cheers
Styx

[jonnyboy]

Just updated git. The results are very good now. The search terms '+ncis +s10* -german' returned 6 pages all having ncis and s10 and none having german.

In the end, the real difference is using the correct operator for each search term.

[styx]

Excellent! So if we could now have some results from some people. I know it helps a lot for me, but it would be good if this change would help all people

cheers
Styx

[jonnyboy]

You can now use a multi column index on name, searchname for fulltext searches.

To use it and not the others, create the fulltext index like:

Code: [Select]

ALTER TABLE releases ADD FULLTEXT ix_releases_name_searchname_ft(name, searchname);


It will only be used if the name is the same and the order of the columns is the same.

[robbiered]

Just as a note this only works on MYISAM for the most part.. Mariadb 10.x supports but seems its untested/optimized for innodb

Code: [Select]

MariaDB [nzed]> ALTER TABLE releases ADD FULLTEXT ix_releases_name_searchname_ft(name, searchname);
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
MariaDB [nzed]>

[jonnyboy]

I use Percona 5.6 and its working perfectly with InnoDB.

Sent from my SGH-I997 using Tapatalk

[robbiered]

Yea seems maria is still a bit behind, they are on 5.5

[jonnyboy]

MariaDB 10 === Mysql 5.6

Sent from my SGH-I997 using Tapatalk