0

This is the reduced query showing problem:

 SELECT 
  t.*
FROM 
  transactions t 
  LEFT JOIN assignments a ON a.transaction_id = t.id 
  LEFT JOIN users u2 ON u2.id = a.user_id  
WHERE (
    u2.operational_type IS NULL
  );

In Production Server (Ubuntu 21.04; mariadb Ver 15.1 Distrib 10.5.13-MariaDB) is executing in 0.0179 seconds. Explain in production server returns:

+------+-------------+-------+--------+----------------+----------------+---------+------------------+-------+-------------------------+
| id   | select_type | table | type   | possible_keys  | key            | key_len | ref              | rows  | Extra                   |
+------+-------------+-------+--------+----------------+----------------+---------+------------------+-------+-------------------------+
|    1 | SIMPLE      | t     | ALL    | NULL           | NULL           | NULL    | NULL             | 56542 |                         |
|    1 | SIMPLE      | a     | ref    | transaction_id | transaction_id | 4       | dbname.t.id      | 1     |                         |
|    1 | SIMPLE      | u2    | eq_ref | PRIMARY        | PRIMARY        | 4       | dbname.a.user_id | 1     | Using where; Not exists |
+------+-------------+-------+--------+----------------+----------------+---------+------------------+-------+-------------------------+

However in developing server (Ubuntu 22.04.1 LTS; mariadb Ver 15.1 Distrib 10.6.7-MariaDB) it gets stuck, In processlist it's state is sending data, it has been more than 30 minutes. Its EXPLAIN is as following:

+------+-------------+-------+--------+----------------+---------+---------+------------------+-------+-------------------------------------------------+
| id   | select_type | table | type   | possible_keys  | key     | key_len | ref              | rows  | Extra                                           |
+------+-------------+-------+--------+----------------+---------+---------+------------------+-------+-------------------------------------------------+
|    1 | SIMPLE      | t     | ALL    | NULL           | NULL    | NULL    | NULL             | 48735 |                                                 |
|    1 | SIMPLE      | a     | ALL    | transaction_id | NULL    | NULL    | NULL             | 1     | Using where; Using join buffer (flat, BNL join) |
|    1 | SIMPLE      | u2    | eq_ref | PRIMARY        | PRIMARY | 4       | dbname.a.user_id | 1     | Using where; Not exists                         |
+------+-------------+-------+--------+----------------+---------+---------+------------------+-------+-------------------------------------------------+

What is the cause? How to troubleshoot?

EDIT: SHOW TABLE STATUS for Production Server.

+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name                  | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| assignments           | InnoDB |      10 | Dynamic    | 50175 |             52 |     2637824 |               0 |      3178496 |   4194304 |          51273 | 2022-07-08 08:18:28 | 2022-08-29 12:40:28 | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N         |
| transactions          | InnoDB |      10 | Dynamic    | 37315 |           1505 |    56180736 |               0 |      3719168 |   7340032 |          41549 | 2022-07-13 11:18:23 | 2022-09-03 15:34:44 | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N         |
| users                 | InnoDB |      10 | Dynamic    |   243 |            741 |      180224 |               0 |        81920 |         0 |            267 | 2022-08-20 12:33:16 | 2022-09-08 11:12:24 | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N   

SHOW TABLE STATUS developing server:

+-----------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name                  | Engine | Version | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment | Max_index_length | Temporary |
| assignments           | InnoDB |      10 | Dynamic    |     0 |              0 |       16384 |               0 |        32768 |   4194304 |          75369 | 2022-09-11 08:05:19 | NULL        | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N         |
| transactions          | InnoDB |      10 | Dynamic    | 48735 |           1497 |    72974336 |               0 |      4767744 |   6291456 |          62236 | 2022-09-11 08:05:15 | NULL        | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N         |
| users                 | InnoDB |      10 | Dynamic    |   275 |            714 |      196608 |               0 |        98304 |         0 |            296 | 2022-09-11 12:09:11 | NULL        | NULL       | utf8mb4_spanish_ci |     NULL |                |         |                0 | N         |

I don't understand why assigments table show 0 rows. SELECT COUNT(*) FROM assignments WHERE 1 returns 74061

12
  • SHOW TABLE STATUS for the 3 tables on each server. (Need to see how big the tables are.) Compare SHOW CREATE TABLE between servers.
    – Rick James
    Commented Sep 11, 2022 at 17:02
  • What is the cause? Different tables statistic. Execute ANALYZE TABLE.
    – Akina
    Commented Sep 11, 2022 at 17:59
  • @RickJames added to question
    – NeDark
    Commented Sep 12, 2022 at 11:49
  • @RickJames SHOW CREATE TABLE only differs in AUTO_INCREMENT
    – NeDark
    Commented Sep 12, 2022 at 11:55
  • @Akina How should I run ANALYZE TABLE? ANALYZE TABLE assignments for example doesn't return useful information imgur.com/a/2AxZgfd
    – NeDark
    Commented Sep 12, 2022 at 12:00

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.