Mariadb sql query get stuck I have table A which size is 32GB and has the following 5 fields:


a (int)
b (varchar(128)
c (varchar(128)
d (int)
e (int) 


a, b and c are unique key. Now, when I query like this:

select 
  * 
from A 
where 
  (
    (a = 1 and b = "something" and c = "something") 
    || 
    (a = 2 and b = "something1" and c = "something1") 
    || ...
    (a = N and b = "something(N-1)" and c = "something(N-1)")
  )


for N = 3000.

When I do explain it says, it using the unique key index, but the query got stuck when I execute. In processlist it's state is sending data.

Why this is happening?

Mysql Explain:

+----+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------------------------+                     
| id | select_type | table            | type  | possible_keys                 | key                           | key_len | ref  | rows | Extra                                         |                     
+----+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------------------------+                     
|  1 | SIMPLE      | repository_files | range | index_repository_files_on_md5 | index_repository_files_on_md5 | 768     | NULL | 1365 | Using index condition; Using where; Using MRR |                     
+----+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+------+-----------------------------------------------+


MariaDB Explain:

+------+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+--------+------------------------------------+
| id   | select_type | table            | type  | possible_keys                 | key                           | key_len | ref  | rows   | Extra                              |
+------+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+--------+------------------------------------+
|    1 | SIMPLE      | repository_files | range | index_repository_files_on_md5 | index_repository_files_on_md5 | 768     | NULL | 224377 | Using index condition; Using where |
+------+-------------+------------------+-------+-------------------------------+-------------------------------+---------+------+--------+------------------------------------+
The answer to your question is that


your MySQL system has 1365 rows
the EXPLAIN for your MariaDB shows 224377 rows
you're now telling me that your running system has ~500,000
535,000 divided by 1365 = ~400
400 * 17 = 6800
6,800 / 60 = 113 minutes ~ 2 hours


First thing to try.

Put separate INDEXes on b and c and see how that works. If not, try below. Are they reasonably distinct - i.e. is their cardinality high? If they are UNIQUE, that would be great.

Second thing to try.

I suggest that you redesign your table A - you should split it up as I suggested earlier. Maybe you should ask a new question in this regard (pointing back to this thread)? Your query is at least O(n) - or maybe worse? Post the output of SHOW CREATE TABLE A\G and some sample data and we'll try and help you with your new query. Also, give the cardinalities of the important fields. 

My suggestion is to keep a, d and e together. Create two new tables B and C and have a PRIMARY KEY in those point back to your new PRIMARY KEY to your new A. Use a new AUTO_INCREMENT as the PK for all three. See how that goes.