Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #376  
Old 25 Jul 2007, 07:07
amcd amcd is offline
 
Join Date: Oct 2004
not only do you not need the fulltext indexes, but also that having them will not give you the full benefit of an external search solution as mysql will continue to spend (waste) time keeping them up to date.
__________________
eXBii.com - Indian community
no XB no fun know XB know fun !
Reply With Quote
  #377  
Old 25 Jul 2007, 08:53
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
Hmmm amcd, you dropped the indexes? I never thought of that.
What exactly you guys did related to this issue? Thanks for your reply.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #378  
Old 25 Jul 2007, 08:55
UK Jimbo's Avatar
UK Jimbo UK Jimbo is offline
 
Join Date: Sep 2002
Originally Posted by TECK View Post
Hmmm amcd, you dropped the indexes? I never thought of that.
What exactly you guys did related to this issue? Thanks for your reply.
Dropping the indexes from the thread and post tables is one of the first things I did after installing sphinx.

Remember to close the forum while you drop the indexes but you should find that inserts to these tables are much faster.
__________________
administrator: visordown.com
Reply With Quote
  #379  
Old 25 Jul 2007, 09:12
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
Thanks for the info.
Can you post the queries?
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #380  
Old 25 Jul 2007, 09:17
UK Jimbo's Avatar
UK Jimbo UK Jimbo is offline
 
Join Date: Sep 2002
If you're using FULLTEXT MySQL search then the word table won't be being used. I'd truncate the word table rather than dropping it just in case.

FULLTEXT search works (from memory) using indexes on the thread and post tables. You can drop those two after cutting over to sphinx.

edit: nice edit there TECK while I was posting
__________________
administrator: visordown.com
Reply With Quote
  #381  
Old 25 Jul 2007, 09:19
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
You are to fast for me.
I edited the previous reply. Could you be kind and post the queries?
I never played before with indexes. Thanks.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #382  
Old 26 Jul 2007, 13:57
PSS PSS is offline
 
Join Date: Jul 2007
Originally Posted by TECK View Post
You are to fast for me.
I edited the previous reply. Could you be kind and post the queries?
I never played before with indexes. Thanks.
TRUNCATE TABLE `PREFIX_word`;

ALTER TABLE `PREFIX_post` DROP INDEX `title`;

KRon's improvements are worth doing, too:

ALTER TABLE `PREFIX_post` ADD INDEX `th_search` ( `threadid` , `visible` , `dateline` )

ALTER TABLE PREFIX_pmreceipt DROP KEY `userid`

ALTER TABLE `PREFIX_pmreceipt` ADD KEY `userid` (`userid`, `readtime`);

ALTER TABLE `PREFIX_post` DROP INDEX `userid`

ALTER TABLE `PREFIX_post` ADD INDEX (userid, dateline);

Last edited by PSS; 26 Jul 2007 at 14:19.
Reply With Quote
  #383  
Old 26 Jul 2007, 14:48
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
Thanks PSS for integrating Kron's MySQL optimizations.
What is the `th_search`? Is not a vBulletin field. Thanks.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #384  
Old 26 Jul 2007, 15:42
amcd amcd is offline
 
Join Date: Oct 2004
Originally Posted by TECK View Post
Thanks PSS for integrating Kron's MySQL optimizations.
What is the `th_search`? Is not a vBulletin field. Thanks.
that is just an index name. you can write anything there.
__________________
eXBii.com - Indian community
no XB no fun know XB know fun !
Reply With Quote
  #385  
Old 27 Jul 2007, 12:56
orban orban is offline
 
Join Date: Jan 2005
I'm leaving this place if somebody wants to take over this thread and keep the guide up to date feel free to do so it's on page 26 I think. Bye.
Reply With Quote
  #386  
Old 27 Jul 2007, 13:19
ALanJay ALanJay is offline
 
Join Date: Jun 2002
Sorry to hear you won't be posting here any more.
Reply With Quote
  #387  
Old 28 Jul 2007, 00:38
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
Originally Posted by orban View Post
I'm leaving this place if somebody wants to take over this thread and keep the guide up to date feel free to do so it's on page 26 I think. Bye.
Why am I not surprised... Today, I've got a 30 points infraction warning for expressing myself freely.
Check my blog for more details.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #388  
Old 31 Jul 2007, 15:26
BigSoccer Tech. BigSoccer Tech. is offline
 
Join Date: Feb 2005
Originally Posted by andrewkhunn View Post
I'd really appreciate that code as well.

On another note, does anyone know if I can use Sphinx to power the similar threads search in vBulletin or will I still need to use the default engine for that. Any pointers here would be much appreciated.
Any ideas on this?
__________________
BigSoccer.com - Share the Passion!
vB.org Board of the Month - September 2007

Tript.com - Share your love of travel today!
Reply With Quote
  #389  
Old 01 Aug 2007, 23:58
BillP BillP is offline
 
Join Date: Nov 2001
I am having problems with my Sphinx search.

I set it up in a basic setting and it is working fine, with a 4-character minimum search. I used the settings and hacks to search.php as described earlier in this thread.

Then I changed VBulletin to allow 3 character words and some exceptions for 2-letter words. I changed sphinx.conf to allow 2 letter words.

I reindexed sphinx, and still can search only for 4+ character words.

Some of the complicating factors: Web server is NOT the searchd server. I do my indexing and run searchd on one host, I run the web server on another host.

Any ideas? I can search using "search" from the CLI and find the 3-letter hits, so I think it has something to do with the way sphinx is shoe-horned in to search.php.
__________________
Bill Plein
Reply With Quote
  #390  
Old 02 Aug 2007, 11:24
Zia's Avatar
Zia Zia is offline
 
Join Date: Dec 2005
Real name: Z.R.
Originally Posted by orban View Post
I'm leaving this place if somebody wants to take over this thread and keep the guide up to date feel free to do so it's on page 26 I think. Bye.
hello...really curious...any one can say..
whats wrong with orban & Orbans Hack (Plugin base templet cache) -the hack moved to graveyard & deleted ?
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 20:33.

Layout Options | Width: Wide Color: