Is adding an index for each column in the denormalized index table really a good idea?

I think we ran into this on a project (on configuration import, many many errors about too many keys specified - max 64 allowed - for adding database indexes for columns in two different search_api_db tables that admittedly do have a ridiculous number of fields.

We will look into this further but any quick thoughts on this old issue?

Including the raw warnings here mostly to help other people who might run into this to find this issue:

 [warning] Drupal\Core\Database\DatabaseExceptionWrapper while trying to add a database index for column field_hh_cori_flag to table search_api_db_waitlist_status: SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed: ALTER TABLE "search_api_db_waitlist_status" ADD INDEX "_field_example_flag" ("field_example_flag"); Array
(
)
 in Drupal\search_api_db\Plugin\search_api\backend\Database->createFieldTable() (line 913 of /var/www/html/web/modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).
 [warning] Drupal\Core\Database\DatabaseExceptionWrapper while trying to add a database index for column field_hh_disability to table search_api_db_waitlist_status: SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed: ALTER TABLE "search_api_db_waitlist_status" ADD INDEX "_field_hh_disability" ("field_hh_disability"); Array

First thing we (Louis) have realized is that rather than adding a field that is not going to be searched or filtered or faceted on in a Search API powered view can simply be left out of the search index, and instead the view can access it from "Example field" rather than "Example field (indexed field)" (that latter one would no longer show up in the Add field dialog on a view).

So hope that helps some one else too!

But there should be an option to not add a field to an index. One use case we ran into is easily / straightforwardly showing related fields of a piece of content in a view, which makes sense to do via Search API rather than a views relationship in a Search API powered view.

There are reasonable use cases (or at least one) for adding a field to a Search API index without wanting it to be … "indexed".

But there should be an option to not add a field to an index. One use case we ran into is easily / straightforwardly showing related fields of a piece of content in a view, which makes sense to do via Search API rather than a views relationship in a Search API powered view.

Technically, this will do exactly the same thing, it just looks different in the UI. I don’t think this is worth adding a new type of “semi-indexed” field to the module.