Adeline Mani-Rajan Adeline Mani-Rajan - 1 month ago 12
MySQL Question

Ajax mysql query working partially

A PHP webpage is calling an AJAX script (querying DB) in pending.
I had to stop it because it was called 2 hours ago.
Whereas I tested it on another DigitalOcean server and it responded in 2 minutes.

The servers are similar (16GB RAM, 8CPU, etc..)

And the there is not much traffic.

I really don't understand. The only differences I noticed are the servers are : MySQL versions and Ubuntu versions.

The 1st server : MySQL Server version: 5.5.52-0ubuntu0.14.04.1 (Ubuntu 14.04.2 LTS).

The 2nd one : MySQL Server version: 5.7.13-0ubuntu0.16.04.2 (Ubuntu 16.04 LTS).

The tables got the same indexes on the same tables that are queried.

Still, I don't get where is the problem ?

Maybe from the SQL query :

SELECT COUNT(*) as max_tok FROM dest WHERE b_id = 1 and statut < 3 and id IN (SELECT dest_id from ouv where created_at > '2016-07-13 00:00:00' and created_at < '2016-10-13 00:00:00')


MySQL Server 1 :

+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dest | 0 | PRIMARY | 1 | id | A | 5994930 | NULL | NULL | | BTREE | | |
| dest | 0 | dest_b_id_mail_index | 1 | b_id | A | 16 | NULL | NULL | | BTREE | | |
| dest | 0 | dest_b_id_mail_index | 2 | mail | A | 5994930 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_statut_index | 1 | statut | A | 16 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_base_id_hash_index | 1 | b_id | A | 16 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_base_id_hash_index | 2 | hash | A | 5994930 | NULL | NULL | | BTREE | | |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


MySQL Server 2 :

+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dest | 0 | PRIMARY | 1 | id | A | 4320721 | NULL | NULL | | BTREE | | |
| dest | 0 | dest_b_id_mail_index | 1 | b_id | A | 1156 | NULL | NULL | | BTREE | | |
| dest| 0 | dest_b_id_mail_index | 2 | mail | A | 4320721 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_statut_index | 1 | statut | A | 3 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_b_id_hash_index | 1 | b_id | A | 942 | NULL | NULL | | BTREE | | |
| dest | 1 | dest_b_id_hash_index | 2 | hash | A | 4320721 | NULL | NULL | | BTREE | | |
+---------------+------------+----------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


(P.S. : the SQL query works on our 2nd server)

Answer

In this particular case, I've migrated to a new DO server (MySQL 5.7.15 and PHP 7.0.12). I've made a back up of my database and files and imported it in the new server.