MySQL Full Text indexes on posts table - any other option?

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Get Involved
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Locked
digitaltoast
Registered User
Posts: 103
Joined: Thu Oct 18, 2007 9:33 am

MySQL Full Text indexes on posts table - any other option?

Post by digitaltoast » Mon May 16, 2011 1:50 pm

I wanted to try offloading the database into the cloud from my poor underpowered VPS.
I found a service called Xeround which is currently in free beta. Looked great, except...
It doesn't allow full text indexes.

If I run the following code, I can see where the problem is:

Code: Select all

SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, CARDINALITY, NULLABLE
FROM information_schema.statistics
WHERE index_type LIKE 'FULLTEXT%' ORDER BY TABLE_SCHEMA, TABLE_NAME

Code: Select all

TABLE_SCHEMA  TABLE_NAME  INDEX_NAME  SEQ_IN_INDEX  COLUMN_NAME  CARDINALITY  NULLABLE       
c4forum	phpbb3_posts	post_content	1	post_subject	1	 
c4forum	phpbb3_posts	post_subject	1	post_subject	1	 
c4forum	phpbb3_posts	post_content	2	post_text	1	 
c4forum	phpbb3_posts	post_text	1	post_text	1
Is there any way round this at all? I notice out of all the DBs running all the different scripts and applications, this one table is the only one indexed like this. As other applications are searchable, how do they do it?
Last edited by Noxwizard on Mon May 16, 2011 11:51 pm, edited 1 time in total.
Reason: Changed topic icon

User avatar
AmigoJack
Registered User
Posts: 5656
Joined: Tue Jun 15, 2010 11:33 am
Location: グリーン ヒル ゾーン
Contact:

Re: MySQL Full Text indexes on posts table - any other optio

Post by AmigoJack » Mon May 16, 2011 2:11 pm

Please understand that this is a phpBB support board, not a DBMS (or more specifically: MySQL) support board. MySQL documentation is available here. phpBB comes with a native search which you can activate in your ACP.
The worst thing about censorship is ███████████
Affin wrote:
Tue Nov 20, 2018 9:51 am
The problem is probably not my English but you do not want to understand correctly.
...
We will not come anybody anyway, nevertheless, it's best to shit this.

digitaltoast
Registered User
Posts: 103
Joined: Thu Oct 18, 2007 9:33 am

Re: MySQL Full Text indexes on posts table - any other optio

Post by digitaltoast » Mon May 16, 2011 2:16 pm

AmigoJack wrote:Please understand that this is a phpBB support board, not a DBMS (or more specifically: MySQL) support board. MySQL documentation is available here. phpBB comes with a native search which you can activate in your ACP.
Hi Amigojack - I can see you're in a foreign country (Green Hill Zone, wherever that is), so perhaps there's a language barrier.
I've posted a question about the ability of phpbb to operate without a FULL TEXT index on the phpbb database running the phpbb board.

So, I hope I don't sound rude, but if you don't fully understand the question, please don't post an irrelevant reply. It wastes time and doesn't help at all.

Pit$Bull
Former Team Member
Posts: 23099
Joined: Sat Dec 02, 2006 4:08 pm
Name: Can't Remember

Re: MySQL Full Text indexes on posts table - any other optio

Post by Pit$Bull » Mon May 16, 2011 2:25 pm

I speak reasonably good English and I saw no specific question regarding not using the search options in phpBB.
Keep this in mind http://www.phpbb.com/rules/#rule4h

Why not just disable the search.

digitaltoast
Registered User
Posts: 103
Joined: Thu Oct 18, 2007 9:33 am

Re: MySQL Full Text indexes on posts table - any other optio

Post by digitaltoast » Mon May 16, 2011 2:35 pm

Pit$Bull wrote:I speak reasonably good English and I saw no specific question regarding not using the search options in phpBB.
Keep this in mind http://www.phpbb.com/rules/#rule4h

Why not just disable the search.
Just disabling the phpbb search doesn't remove the full text index from that column in the phpbb database.
I see a lot of posts from people having problems ADDING the full text index. I don't want to delete it if this is going to cause some problem in the future.

Thanks!

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 50917
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: MySQL Full Text indexes on posts table - any other optio

Post by stevemaury » Mon May 16, 2011 2:51 pm

Well, you can delete the fulltext index in the Maintenance tab of the ACP, but you would have to ask the Xeround people how you would then make the database searchable.
For REALLY good and VERY inexpensive hosting CLICK HERE

I can stop all your spam. I can upgrade or update your Board. PM or email me. (Paid support)

User avatar
D¡cky
Former Team Member
Posts: 11812
Joined: Tue Jan 25, 2005 8:38 pm
Location: New Hampshire, USA
Name: Richard Foote
Contact:

Re: MySQL Full Text indexes on posts table - any other optio

Post by D¡cky » Mon May 16, 2011 4:44 pm

As AmigoJack pointed out, you can switch your search index to Fulltext Native which does not require the fulltext indexes on the posts table.

In the Administration Control Panel under the General tab, click Search Settings and set Search Backend to Fulltext native.

You can manually drop the fulltext indexes since they are not required for the Fulltext native.

Code: Select all

ALTER TABLE phpbb3_posts DROP INDEX post_subject;
ALTER TABLE phpbb3_posts DROP INDEX post_text;
ALTER TABLE phpbb3_posts DROP INDEX post_content;
After switching the Search Backend, you will need to rebuild the Search Index under the Maintenance tab -> Search Index
Have you hugged someone today?

digitaltoast
Registered User
Posts: 103
Joined: Thu Oct 18, 2007 9:33 am

Re: MySQL Full Text indexes on posts table - any other optio

Post by digitaltoast » Mon May 16, 2011 4:47 pm

Ah, right, that's what I was after - that it's safe to drop the index without future problems.

Thank you - question answered! :)
D¡cky wrote:As AmigoJack pointed out, you can switch your search index to Fulltext Native which does not require the fulltext indexes on the posts table.

In the Administration Control Panel under the General tab, click Search Settings and set Search Backend to Fulltext native.

You can manually drop the fulltext indexes since they are not required for the Fulltext native.

Code: Select all

ALTER TABLE phpbb3_posts DROP INDEX post_subject;
ALTER TABLE phpbb3_posts DROP INDEX post_text;
ALTER TABLE phpbb3_posts DROP INDEX post_content;
After switching the Search Backend, you will need to rebuild the Search Index under the Maintenance tab -> Search Index

Locked

Return to “[3.0.x] Support Forum”