I ran an EXPLAIN on a slow (2 minutes to return 2 sorted results) query in MariaDB, and some of the returned columns contain multiple values separated by a "|" symbol. When using a better index (same query running in 20ms), EXPLAIN returns similar values but separated by a comma.
I spent the last hour looking for any kind of reference online, both in the MariaDB and MySQL documentation (since I'm not sure it's MariaDB-specific), but nothing relevant came up - not even a SO question.
Do you know what the "|" symbol means in this context? Considering the time difference vs the comma-separated result it feels like a combinatory operator, but adding "combinatory" or "exponential" as google search key didn't provide any additional insight. EXPLAIN EXTENDED followed by SHOW WARNINGS didn't provide any additional insight either.
Return fields examples:
TYPE: ref|filter
KEY: key1|key2
KEY_LEN: 9|9
Rows: 2 (0%)
Extra: Using where; Using rowid filter
Thank you for any input!
EDIT: for additional context, here's the hibernate-generated query that produces the result above:
select * from things this_ left outer join rel_tab rt_ on this_.id=rt_.thing_id left outer join tab2 t2_ on this_.id=t2_.thing_id where this_.filter1=123 and this_.filter2=456 and this_.filter3=1 order by this_.id desc limit 20;
I also updated the explain plan result above with the filter selectivity.