How to multi-valued indexes on mariadb

[timelabs-lucvt]

I have a promotions table and a product_ids column containing a list of products. When I do a migration, I get the same error.

• Migration:

Schema::table('promotions', function (Blueprint $table) { $table->rawIndex('(cast(product_ids as unsigned array))', 'product_ids_index'); });

• Error:

  You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(cast(product_ids as unsigned array)))' at line 1

Is there any other way to fix it? Thank you!

[staudenmeir]

Hi @timelabs-lucvt, MariaDB doesn't support multi-valued indexes, unfortunately.

[timelabs-lucvt]

@staudenmeir So is there any way to solve it other than changing the data structure?

[staudenmeir]

There's no alternative that I know of.

Are you having performance issues?

[timelabs-lucvt]

That's right. I am querying for products that are not yet part of any promotions. And it's quite slow

[staudenmeir]

What query are you running in this case?

[timelabs-lucvt]

In the product model I define the hasManyJson promotions relationship

and I use query

[staudenmeir]

Did you already log the executed SQL and analyze it with EXPLAIN?

Maybe you can improve the query performance with indexes on the time or status columns (if you haven't already). If the lookup in the product_ids column is the actual bottleneck, I don't really see any solutions besides changing the data structure.

[timelabs-lucvt]

I have indexed the time and status but the query is still quite slow. I decided to change the data structure and it got better. Thanks for your support