Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Search Performance

Quote Reply
Search Performance
Hi,

We have a very large database of just under 4 million links. Our directory is running great, but searches are taking a long time.

Single keyword searches are just about bearable, but multiple keyword searches are taking a very long time unless those particular searches have already been made as we cache the results.

What is the best way to optimize LinksSQL and how could we squeeze every last drop of performance gain we can out of the search?

And ideas and suggestions are very welcome.

Thanks in advance,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
You running INTERNAL INDEXING ?

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Search Performance In reply to
Could you explain a bit more about this? What is INTERNAL INDEXING? Are you referring to a MySQL feature, or a LinksSQL feature?

Thanks,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
Log in via SSH, and type;

perl nph-index.cgi --change-driver=INTERNAL

then;

perl nph-index.cgi --reindex

Speeds up searches by a huge amount.

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [Andy] Search Performance In reply to
OK, I will try this. Thankyou.

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
You may also want to have a look at the alternatives - I think that the mysql indexing is supposed to be good in mysql4. I think there is a section in the docs on the pros and cons of each choice.
Quote Reply
Re: [afinlr] Search Performance In reply to
Well, it was running on MySQL 4 indexing before, so I'll try the method mentioned above and see if it makes any difference. If not, or if it's worse then I guess I'll have to go back to MySQL 4 indexing.

Thanks,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
Well, after only just finishing the conversion to internal indexing which took about 6.5 hours the serach no longer runs at all. It's giving the error:

Quote:
Failed to execute query: 'SELECT *,MATCH(URL,Description,Title) AGAINST (' +computer*') AS score FROM lsql_Links WHERE ( ( isValidated = ? ) AND ( MATCH( URL,Description,Title ) AGAINST (' +computer*' IN BOOLEAN MODE) ) )
ORDER BY score desc
LIMIT 0, 20' Reason: Can't find FULLTEXT index matching the column list at /home/bn23/bn23.com/cgi-bin/portal/admin/GT/SQL/Search/MYSQL/VER4.pm line 230.

So, do I have to run ./nph-index.cgi --change-driver=MYSQL to put it mack to MySQL FullText indexing or is there a way to fix the above mentioned error?

Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
Ok, ./nph-index.cgi --change-driver=MYSQL gives the following message:

Quote:
Updating Links Table search driver...
Error: Search Driver (MYSQL) is the same.

Updating Category Table search driver...

There were problems switching drivers.

How can I fix the problem that i mentioned in my last post if it wont even let me switch back to mysql indexing? Getting kinda desperate to get this fixed now.

Thanks,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
Hi,

With MySQL 4 which tables/columns should have FULLTEXT indexing on them?

Thanks,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories
Quote Reply
Re: [b0rdslide] Search Performance In reply to
I've now added FULLTEXT indexing back to the title, url and description columns (the description column took almost 24 hours to complete!), yet I'm still getting the error that I mentioned above. What other columns need fulltext for the search to work?

Is there anything else that needs to be done to fix this?

Thanks,
Kev

-------
BN23.com - LinksSQL powered search and directory.
5.2 Million Links, 685,000 Categories