MySQL Bugs: #112346: MEMBER_OF with multi-valued index on JSON not used in 99% of situations.

Bug #112346	MEMBER_OF with multi-valued index on JSON not used in 99% of situations.
Submitted:	14 Sep 2023 8:21	Modified:	11 Oct 2023 7:22
Reporter:	Daniel Cohen Gindi	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S5 (Performance)
Version:	8.0.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using a MEMBER OF (or JSON_CONTAINS etc.) with a multi-valued index on JSON, it's not used in most situations.
It is only used when used in a WHERE directly with a raw, pre-determined value.
Not used in a value taken from a query, from current table, from a JOIN, or even a simple RAND().

This makes multi-value indexes only effective in the documentation examples. Not in the real world.

How to repeat:
CREATE TABLE if not exists customers(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(64), zips JSON);

ALTER TABLE customers  ADD INDEX ix_zips1( (CAST(zips AS UNSIGNED ARRAY)) );
ALTER TABLE customers  ADD INDEX ix_zips2( (CAST(zips->'$' AS UNSIGNED ARRAY)) );
ALTER TABLE customers  ADD INDEX ix_zips3( (CAST(zips->'$[*]' AS UNSIGNED ARRAY)) );

/* INSERT LOTS OF RANOM DATA, with `zips` as an array of numbers */

EXPLAIN SELECT * from customers WHERE 12345 MEMBER OF (zips); --> The only situation when the index is used
EXPLAIN SELECT * from customers WHERE floor(rand() * 10000 + 10000) MEMBER OF (zips); --> NOT using index
EXPLAIN SELECT customers.*,c2.zips FROM customers
inner join customers c2 on customers.id MEMBER OF (c2.zips); --> NOT using index

Any other example that comes to mind.

Hello  Daniel,

Thank you for the report and feedback.
Could you please provide subset of the data(logical dump of the table involved and preferably taken using mysqldump etc. ) to reproduce the issue? Thank you.

If the data you need to attach is more than 50MB, you should create a compressed archive of the data, split it to 50MB chunks, and upload each of them as a separate attachment. More details on how to upload are provided in the "Files" section of this bug page. Thank you.

regards,
Umesh

A sample data, including index creation

Attachment: sample_data.sql (application/octet-stream, text), 3.38 MiB.

Hello Daniel,

Thank you for the report and feedback.

regards,
Umesh