CODEWITHSUNDEEP
mysqlmariadb-10.5
Sinnbeck
Sinnbeck

Reputation: 667

MariaDB crashes when using large WHERE IN

I have an old project that I am trying to upgrade to a newer version of SQL. It is currently running MySql 5.5 and is working just fine. I have migrated som test data to MariaDB 10.5.9, but when I try running a query (which works fine on MySql 5.5), MariaDB crashes.

The query is quite big, and uses WHERE IN extensively. Currently I am unable to refactor the query sadly, so I am instead trying to figure out what causes the crash.

It has 3 WHERE IN. First is 24 items, second is 696 and third is 2. If I remove just one item from either the first or second WHERE IN, it works are returns data instantly. The answers table is MyISAM

The error I am getting

SQL Error [08S01]: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

And the query

SELECT
    definition_id,
    answer AS value
FROM
    answers
WHERE
    definition_id IN (...)
    AND respondent_id in (...)
    AND context IN (1, 0)
LIMIT 50

I have already tried changing max_allowed_packet to something higher (it was 16MB in 5.5), but it sadly changes nothing.

Result of EXPLAIN SQL_NO_CACHE (if I remove alot of the data in the WHERE IN to avoid crash)

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ALL 824 Using temporary; Using filesort
2 DERIVED s1 range definition_respondent_context,respondent_id definition_respondent_context 12 824 Using index condition; Using temporary; Using filesort
2 DERIVED s2 eq_ref definition_respondent_context,respondent_id definition_respondent_context 12 const,database_name.s1.respondent_id,const 1

EDIT: I managed to get it working by using a join on the definitions table

SELECT
    a.definition_id,
    a.answer AS value
FROM
    answers AS a
JOIN definitions AS d ON a.definition_id = d.id
WHERE
    d.id IN (...)
    AND a.respondent_id in (...)
    AND a.context IN (1, 0)
LIMIT 50

Upvotes: 1

Views: 884

Answers (2)

Dinesh vishe
Dinesh vishe

Reputation: 3598

CREATE INDEX index_sales ON sales (context);

Create this index, so data can be retrieved faster and resource will be reduced.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522516

One solution to your problem is to change your design/approach such that you don't have WHERE IN (...) clauses with 500-1000 items in them. For one thing, it's doubtful that you would ever have some application passing so many parameters back to your database instance. So, assuming this data is not coming from the outside, then it should be possible to maintain it in a separate table. Assuming you had two tables for this, your query could then become:

SELECT a.definition_id, a.answer AS value
FROM answers a
INNER JOIN definitions d
    ON d.id = a.definition_id
INNER JOIN respondents r
    ON r.id = a.respondent_id
WHERE
    context IN (1, 0)
-- ORDER BY <something>
LIMIT 50;

Upvotes: 1

Related Questions