MySQL Filter Index Missing for Element

[pastvne]

Hi All:
I have an element that is type field.
When I originally created it, I did not think I would need to filter based upon it so I never defined a filter in the element properties.
Later, I realized I would like to have a filter for it of a text type

I added the filter specification to the elements properties but when looking at the MySQL structure, I see no filter_ index has been created.

I wondered if there is a way that I should use Fabrik to re-create the filter_ index or if it is safe for me to simply create it at the MySQL level?

Thanks,
Bruce

 

[jmoises]

have you check the List Settings for the filters?

 

[troester]

As soon as you select a "Filter type" in element's List view settings Fabrik is adding an index fb_filter_your-column_INDEX
Isn't this done on your site?
But you can also/addtionally create one directly in your database.

 

[pastvne]

Hello Jmoises:
Thanks for the reply. I believe you are asking if I enable a filter on that element and to verify that by checking the List view settings of that element. The settings are: filter type = Field, Length=20, Filter Access = site_admin, full word match = yes, required=no, filter_data=Use Global, Include in Advanced search= Yes, Filter Class = Input-medium, Class = visible.

I believe this should have created both the order_ and filter_ indexes.

thanks,
Bruce

have you check the List Settings for the filters?

 

[pastvne]

As soon as you select a "Filter type" in element's List view settings Fabrik is adding an index fb_filter_your-column_INDEX
Isn't this done on your site?
But you can also/addtionally create one directly in your database.

I have a lot of fields and I see what you describe happening for each of the other fields. But for this field, the filter_ index is missing. That was probably normal when I created the field since I had not asked for a filter. But I was expecting that when I added the filter under list view settings that Fabrik would create it, which it didn't. I'll try again to remove the filter in Fabrik then re-add it to see if perhaps I had mangle the index at the MySQL level without realizing it. then, if that fails, I'll create the index using the naming pattern I see for the other indexes that Fabrik created. Thanks for verifying that it is fine to manually create an index.

 

[pastvne]

Update: Tried to move the filter value to None, then save the element, then move back to field. That did not result in a filter_ index being created. It seems like there is something, for this element, preventing the filter_ index from being re-created. For now, I'll create the Index manually.

 

[troester]

Out of interest: what is the name of your element/column? Maybe some sort of "reserved" word.
BTW:
If there's already an other INDEX for this column (e.g. fb_order_... or fb_prefilter_... ) Fabrik won't create an additional fb_filter... one (one index per column is enough, the index name doesn't matter).

 

[pastvne]

Out of interest: what is the name of your element/column? Maybe some sort of "reserved" word.
BTW:
If there's already an other INDEX for this column (e.g. fb_order_... or fb_prefilter_... ) Fabrik won't create an additional fb_filter... one (one index per column is enough, the index name doesn't matter).

Hi Troester: the name is csp_document_control___doc_reference. The Order index is there, just not the filter index. This could explain the issue. Perhaps over time I lost the filter index but not the order index. I do recall having to fix the database once and perhaps I lost an index then and didn't notice it. But that would explain it if Fabrik does not try to create the _filter index if there is already an order index.

At any rate, creating the index manually did the trick for me... thanks!

Thinking this through some more. I noticed when I created the index manually that it also behaved oddly... as if the index was not there. I have to confess that I'm not an indexing expert. So, realized that I was using exact match and no full word in the filter options and wondered if the size param on the index could be impacting behavior (the value in this column is longer than 10 and the first 10 character can be repeated across a lot of rows in the database. I upped the size of the index to 20 so that it was longer than any value in the column. The cardinality increased and the index became fast. So, I'm now wondering if the whole problem was that, while I had one index (order_) that could have worked, since it was sized at 10 and not 20, that each node of the index was so large as to make the use of the index almost worthless. I wonder how Fabrik chooses a size for an index and whether my theory might have some validity?