Page 1 of 1

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

Posted: Mon May 16, 2011 1:50 pm
by digitaltoast
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?

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

Posted: Mon May 16, 2011 2:11 pm
by AmigoJack
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.

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

Posted: Mon May 16, 2011 2:16 pm
by digitaltoast
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.

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

Posted: Mon May 16, 2011 2:25 pm
by Pit$Bull
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.

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

Posted: Mon May 16, 2011 2:35 pm
by digitaltoast
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!

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

Posted: Mon May 16, 2011 2:51 pm
by stevemaury
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.

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

Posted: Mon May 16, 2011 4:44 pm
by D¡cky
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

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

Posted: Mon May 16, 2011 4:47 pm
by digitaltoast
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