CMSMS DB tables and MySQL FULLTEXT capabilities
Posted: Thu Dec 11, 2008 5:55 pm
Hi there,
I am currently working on my content_dump plug-in to make it filter for specific content_prop contents. Following the philosophie of the plug-in, I want to solve it already on a database query level.
As far as I understood, there are basically two options (simplified).
a) Use MySQL "LIKE" in single props - Where content LIKE '%string%'
b) Use FULLTEXT indices - Where MATCH (content) against ('string')
However, I think LIKE is a little slow when it comes to large tables. So I went for FULLTEXT. As far as I understand the database layout, the content_props table doesn't feature a fulltext index. Thus I added it manually to the DB as:
Now I can query like this:
I tested it on two back-ups of smaller sites and it seems to work with no hassle. However, now as the database layout has been changed, I know I am on my own when it comes to odd behaviour as the tables are no longer structured as they were provided.
Well, personally, I can live with that
- I just wondered, whether this change could be added to the next version of CMSMS. Before I post a feature request, I wanted to hear if I have overlooked something. Does such an index already exist elsewhere? Does something speak against adding such an index to the provided database layout?
I am looking forward to your feedback.
Best regards
Nils
I am currently working on my content_dump plug-in to make it filter for specific content_prop contents. Following the philosophie of the plug-in, I want to solve it already on a database query level.
As far as I understood, there are basically two options (simplified).
a) Use MySQL "LIKE" in single props - Where content LIKE '%string%'
b) Use FULLTEXT indices - Where MATCH (content) against ('string')
However, I think LIKE is a little slow when it comes to large tables. So I went for FULLTEXT. As far as I understand the database layout, the content_props table doesn't feature a fulltext index. Thus I added it manually to the DB as:
Code: Select all
ALTER TABLE prefix_content_props ADD FULLTEXT(content);
Code: Select all
$q = ... AND match(content) against('foo -bar' in boolean mode) ... ;
Well, personally, I can live with that
- I just wondered, whether this change could be added to the next version of CMSMS. Before I post a feature request, I wanted to hear if I have overlooked something. Does such an index already exist elsewhere? Does something speak against adding such an index to the provided database layout?I am looking forward to your feedback.
Best regards
Nils