CMSMS DB tables and MySQL FULLTEXT capabilities

[nhaack]

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:

Code: Select all

ALTER TABLE prefix_content_props ADD FULLTEXT(content); 

Now I can query like this:

Code: Select all

$q = ... AND match(content) against('foo -bar' in boolean mode) ... ;

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