0

Consider the following queries:

SELECT uID, name FROM table WHERE JSON_CONTAINS(email, '{"key": "email@email.com"}', '$');
SELECT uID, name, history FROM table WHERE JSON_CONTAINS(email, '{"key": "email@email.com"}', '$');
SELECT uID, name, history FROM table WHERE uID = 1;

uID, name, and history columns are INT(10), JSON, and JSON datatypes, respectively. email column is also JSON datatype.

Queries 1 and 3 both complete in a moment. Query 2 times out after 30 seconds (Error Code: 2013. Lost connection to MySQL server during query).

My question is this: Why is it that combining a SELECT history and WHERE JSON_CONTAINS causes a timeout, whereas having either or in the query is fine?

JWolfgang
  • 1
  • Do you have a lot of rows? This could be a painful table scan. Check with `SHOW PROCESSLIST` to see how long it's taking. – tadman Oct 23 '20 at 01:51
  • genrated an index on $.key see https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html – nbk Oct 23 '20 at 02:11
  • @tadman There's over 60k rows, however I'm not sure why that would affect this particular case, since query #1 works perfectly fine. – JWolfgang Oct 23 '20 at 16:43
  • Time for `EXPLAIN` to help, presumably. – tadman Oct 23 '20 at 18:12
  • @tadman Using `EXPLAIN` doesn't reveal much, if anything. Including `SHOW WARNINGS` gives me a `Code 1003`, which according to mariadb documentation just means `YES` which is obviously not very helpful. As far as I can tell from using `EXPLAIN` there's nothing wrong with these queries. – JWolfgang Oct 27 '20 at 17:56

0 Answers0