Massive speed improvements -- details within

[jonnyboy]

The same queries used for MyIsam does not seem to return any results when used with the Percona 5.6.12 InnoDB. I will try MySQL current version tomorrow. Where this works(I assume) in MyIsam:

Code: [Select]

SELECT name FROM releases WHERE MATCH(name) AGAINST('"Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264"' IN BOOLEAN MODE);It returns 0 results in InnoDB. A minor change;

Code: [Select]

SELECT name FROM releases WHERE MATCH(name) AGAINST('+Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264' IN BOOLEAN MODE);
The problem, as I see it, is it doesn't return only an exact match. But, this could be close enough for a match.

Code: [Select]

+------------------------------------------------------------------------------------------------------------------------------------------------+
| name                                                                                                                                           |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHDIce.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHD.mkvf                          |
| Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHDIce.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHD.mkv.par2f                     |
| [140919]-[FULL]-[#a.b.teevee]-[ Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHD ]-[02/33] - "ice.cold.gold.s01e06.720p-dhd.par2" yEnc |
| [140919]-[FULL]-[#a.b.teevee]-[ Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHD ]-[09/33] - "ice.cold.gold.s01e06.720p-dhd.r06" yEnc  |
| [140919]-[FULL]-[#a.b.teevee]-[ Ice.Cold.Gold.S01E06.Redemption.Ridge.720p.HDTV.x264-DHD ]-[01/33] - "ice.cold.gold.s01e06.720p-dhd.nfo" yEnc  |
+------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.28 sec)

Testing this script to rename everything to predb names. Not blazingly fast, but better than testing 100's of regexes.

[djh82uk]

I get this when I try to run that SQL (running Percona on Myiasm)

Code: [Select]

Error
SQL query:

CREATE INDEX ix_releases_searchname ON releases(

searchname
);

MySQL said: Documentation

#1061 - Duplicate key name 'ix_releases_searchname'

[jonnyboy]

means you have to drop the index before you can create another with the same name. Or, you have releases duplicate searchnames, you could reset searchnames first.

[benpoepen]

I wonder if it's doable to simply use 2 databases.
1 for the inserts&processing which uses innodb
a 2nd one for the site itself, using myisam with fulltextindex.

Database 1 could replicate to database 2 and done

(or... implement sphinx-support. This is one thing which helped NN+ a LOT)

[Saner]

You can use fulltext on newer versions of innodb 5.6 + (IIRC)

[styx]

Sorry for digging out this old thread, but speed improvements are always exciting :-)

If someone is reading this, he/she might have noticed that the changes mentioned here are not really possible anymore, as the releases.php file has changed a lot in the meantime. However, this might help you anyhow.

1. Make sure you create the original full-text index, as shown in the first post (this might take a bit)
2. Edit releases.php, search for the public function searchSQL
3. Add another "else if" statement:

Code: [Select]

                                        else if (substr($word, 0, 2) == '!!')
                                                $searchsql .= sprintf(' AND match(releases.%s) against (%s)', $type, $db->escapeString('%'.substr($word, 2).'%'));
4. Save and close

If you now enter a search preceded with "!!", you'll do a search on the full text index you've created.

Some example for my machine, with searches for random strings (in seconds):
oLkgMCiAMHwjyMPP = 21.77
98wkTStp3gU2Icaq = 23.15
BTyIsv9qgCf1NCAe = 18.91
!!2huZ7DzCc8CSKPRr = 0.109
!!eiQfEn928HUMy61C = 0.119
!!YMEfutYL5qaQcODv = 0.103

So, it is faster.

Drawbacks:
1. It's just on the site, when you use !!, not in all searches
2. It can just search for one word, probably easy to change
3. As you can see in the previous part of the post, this only works in MyISAM

For me, it helps. Be sure to backup your releases.php before, I don't take any responsibility.

It would really be wonderful if something like this could be build into nZEDb. For me, the system is doing very well, except that searches tend to be slow. Something like this could be helpful. However, I'm not a coder, so I cannot really help with that. Should someone be able to pick up here - it would be great!

Thanks for your attention!

Cheers
Styx

[styx]

Following up my previous post, here is a small update.

WARNING: this has only been tested with MyISAM tables, previous information in this thread suggests that this might break stuff with InnoDB! Use at your own risk!

WARNING: ^ search and -- search do not work with this code for the moment!

As I am using MyISAM (InoodDB not required for inserting speed, system is fast enough), I've now replaced the searchSQL function in releases.php with next code:

Code: [Select]

        public function searchSQL($search, $db, $type)
        {
                $words = explode(' ', $search);
                $searchsql = '';
                $searchwords = '';
                $intwordcount = 0;
                if (count($words) > 0)
                {
                        $like = 'ILIKE';
                        if ($db->dbSystem() == 'mysql')
                                $like = 'LIKE';
                        {
                        foreach($words as $word)
                        {
                                $searchwords .= sprintf(' +%s',$word);
                        }
                        $searchsql .= sprintf(' AND match(releases.%s) against (\'%s\' in boolean mode)',$type,$searchwords);
                        }
                }
                return $searchsql;
        }

The speed improvements are like previously, it's just a change to the default search to use the MyISAM full text indexes. Searches run very speedy now on my machine. Next thing is that I have to understand what the API calls are using. If anyone can point me in the right direction, it would be much appreciated.

Cheers
Styx

[jonnyboy]

I have added this to site search, dev branch and will be pushed later today.
This works only if you have these indexes - ix_releases_searchname_ft and/or ix_releases_name_ft.
If you search on releases.name - ix_releases_name_ft is required
If you search on releases.searchname - ix_releases_searchname_ft is required

Note**
This does slow down other scripts, such as renametopre.php, as updating both of the ft indexes takes a bit of time. Granted it is still fast, just note the increased time for updating searchname.

[styx]

Hello again,

My previous changes can be done even easier (sorry Jonnyboy, I think you'll have to update again):

Replace

Code: [Select]

$searchsql .= sprintf(' AND releases.%s %s %s', $type, $like, $db->escapeString('%'.$word.'%'));with

Code: [Select]

$searchsql .= sprintf(' AND match (releases.%s) against (+\'%s*\' in boolean mode)', $type, $word);
Please test and stuff. I think the previous might break something (API IMDB search seems to behave erratic), this should be better.

Disclaimer:
1. It needs the full text index, as defined in the first post
2. As there is an extra index, this makes the other scripts a bit slower
3. It works with MyISAM, not with InnoDB (needs testing with InnoDB 5.6, please post results)
4. I'm not a coder, code might look like crap, needs someone to go over it and check!

cheers
Styx

[styx]

BTW, also note this page: http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html - it talks about the minimum length of a FT search, which seems to be set to 4 characters. The page gives enough information to change if you like. Basically:
1. Set new value in my.cnf
2. Stop server
3. Start server
4. Drop index
5. Create index

cheers
Styx

[jonnyboy]

This works with current version of MySQL/Percona 5.6 InnoDB.

[styx]

On InnoDB as well?

[jonnyboy]

I would appreciate it if someone would post site search times from before and after adding these indexes. Thanks

[jonnyboy]

On InnoDB as well?

Yes. I am running all tables InnoDB, Percona 5.6.15-63.

[jonnyboy]

I tried changing the query and the results were not good. The search 'ncis s10' with the query as it is in git, returned 12 hits, but only releases with 'ncis s10', not 'ncis.s10'.

With the change, the search returned 4 pages of results, none of which had 'ncis' or 's10' in the title.

Without the full text index, I get 2 pages or releases, all of which have 'ncis' and 's10' in the title.

This will take some reading and tweaking of the script to get better results.