Author Topic: Massive speed improvements -- details within  (Read 195381 times)

Offline styx

  • Junior Indexer
  • **
  • Posts: 14
  • Helpful: +0/-0
Re: Massive speed improvements -- details within
« Reply #30 on: 2014-01-18, 03:20:20 PM »
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

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
Re: Massive speed improvements -- details within
« Reply #31 on: 2014-01-18, 03:35:35 PM »
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.
« Last Edit: 2014-01-18, 03:45:49 PM by jonnyboy »

Offline styx

  • Junior Indexer
  • **
  • Posts: 14
  • Helpful: +0/-0
Re: Massive speed improvements -- details within
« Reply #32 on: 2014-01-18, 09:34:06 PM »
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

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
Re: Massive speed improvements -- details within
« Reply #33 on: 2014-01-20, 02:12:41 PM »
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.

Offline robbiered

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: Massive speed improvements -- details within
« Reply #34 on: 2014-01-20, 08:59:34 PM »
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]>

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
Re: Massive speed improvements -- details within
« Reply #35 on: 2014-01-21, 01:24:16 AM »
I use Percona 5.6 and its working perfectly with InnoDB.

Sent from my SGH-I997 using Tapatalk


Offline robbiered

  • Junior Indexer
  • **
  • Posts: 35
  • Helpful: +1/-0
Re: Massive speed improvements -- details within
« Reply #36 on: 2014-01-21, 12:21:06 PM »
Yea seems maria is still a bit behind, they are on 5.5

Offline jonnyboy

  • Epic Indexer
  • *****
  • Posts: 1046
  • Helpful: +93/-1
  • Lazzy Trucker
    • nZEDb
Re: Massive speed improvements -- details within
« Reply #37 on: 2014-01-21, 12:22:40 PM »
MariaDB 10 === Mysql 5.6

Sent from my SGH-I997 using Tapatalk