MySQL Forums
Forum List  »  Optimizer & Parser

Why multi-valued index is not in possible keys in a dependent subquery
Posted by: ELON ZHOU
Date: February 02, 2023 10:01PM

-- DDL
create table scholardata_journal
id int auto_increment primary key,
sci_if double null,
issn_list json default (_utf8mb4'[]') not null

create index issn_list_multi_value_index
on journal ((cast(`issn_list` as char(15) array)));

create table userlibrary_biblio
id int auto_increment primary key,
user_id int NOT NULL,
issn varchar(50) not null,

constraint userlibrary_biblio_user_id_ecab5d00_fk_auth_user_id
foreign key (user_id) references ivy.auth_user (id)

-- SELECT in subquery
select id,
IF(userlibrary_biblio.issn = '', null, (select sci_if
from scholardata_journal
where userlibrary_biblio.issn member of(scholardata_journal.issn_list)
limit 1))
) as sci_if
from userlibrary_biblio where user_id = 28;

Explain result:

-> Index lookup on userlibrary_biblio using userlibrary_biblio_user_id_ecab5d00_fk_auth_user_id (user_id=28) (cost=260.78 rows=254) (actual time=0.098..1.185 rows=254 loops=1)
-> Select #2 (subquery in projection; dependent)
-> Limit: 1 row(s) (cost=5875.65 rows=1) (actual time=19.485..19.486 rows=1 loops=84)
-> Filter: userlibrary_biblio.issn member of (scholardata_journal.issn_list) (cost=5875.65 rows=56274) (actual time=19.485..19.485 rows=1 loops=84)
-> Table scan on scholardata_journal (cost=5875.65 rows=56274) (actual time=0.005..13.855 rows=22979 loops=84)

Options: ReplyQuote

Written By
Why multi-valued index is not in possible keys in a dependent subquery
February 02, 2023 10:01PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.