Mysql Queries cause high load in server

Posted on

Question :

I have problem in the database ,here is phpmyadmin status queries

--------------------+                                                                                                                    53:22
| Id   | User         | Host      | db           | Command | Time | State        | Info
+------+--------------+-----------+--------------+---------+------+--------------+-----------------------------------------------------------------
--------------------+
| Id   | User         | Host      | db           | Command | Time | State        | Info
                    |
+------+--------------+-----------+--------------+---------+------+--------------+-----------------------------------------------------------------
--------------------+
| 2667 | leechprotect | localhost | leechprotect | Sleep   | 508  |              |
                    |
| 4354 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4357 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4358 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4359 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4362 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4363 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |
| 4364 | Data_User   | localhost | Data_Name   | Query   | 86   | Sending data | SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW
()-INTERVAL 24 HOUR |

here is sql queires

SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;
# User@Host: Data_User[Data_User] @ localhost []
# Query_time: 94.999872  Lock_time: 0.000046 Rows_sent: 1  Rows_examined: 466611
SET timestamp=1401044204;
SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;
# User@Host: Data_User[Data_User] @ localhost []
# Query_time: 94.120904  Lock_time: 0.000030 Rows_sent: 1  Rows_examined: 466613
SET timestamp=1401044204;
SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;
# Time: 140525 20:56:45
# User@Host: Data_User[Data_User] @ localhost []
# Query_time: 95.266550  Lock_time: 0.000032 Rows_sent: 1  Rows_examined: 466612
SET timestamp=1401044205;
SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;
# User@Host: Data_User[Data_User] @ localhost []
# Query_time: 95.262194  Lock_time: 0.000029 Rows_sent: 1  Rows_examined: 466612
SET timestamp=1401044205;
SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;
# User@Host: Data_User[Data_User] @ localhost []
# Query_time: 94.973241  Lock_time: 0.000031 Rows_sent: 1  Rows_examined: 466612
SET timestamp=1401044205;
SELECT SUM(money) FROM IP2Files WHERE usr_id='0' AND created>NOW()-INTERVAL 24 HOUR;

How could I fix the IP2Files table ?

update :

mysql> SHOW CREATE TABLE IP2Files;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| IP2Files | CREATE TABLE `IP2Files` (
  `file_id` int(10) unsigned NOT NULL DEFAULT '0',
  `ip` int(20) unsigned NOT NULL DEFAULT '0',
  `usr_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `owner_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `size` bigint(20) unsigned NOT NULL DEFAULT '0',
  `money` decimal(8,4) unsigned NOT NULL DEFAULT '0.0000',
  `referer` varchar(255) NOT NULL DEFAULT '',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`file_id`,`ip`,`usr_id`),
  KEY `owner` (`owner_id`),
  KEY `user` (`usr_id`),
  KEY `ip` (`ip`,`created`),
  KEY `date` (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)




mysql> EXPLAIN SELECT SUM(money) FROM IP2Files;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | IP2Files | ALL  | NULL          | NULL | NULL    | NULL | 446977 |       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)

mysql>




mysql> SELECT COUNT(*) FROM IP2Files WHERE usr_id=0 AND created>NOW()-INTERVAL 24 HOUR;
+----------+
| COUNT(*) |
+----------+
|   114001 |
+----------+
1 row in set (0.59 sec)

Answer :

As expected (from the 90+ seconds they were spending), the queries are doing a full table scan, reading all 460K rows.

Add an index on (usr_id, created, money) and it will be substantially faster:

ALTER TABLE databasename.IP2Files
  ADD INDEX usr_id_created_money_IX      -- pick a name for the index
    ON (usr_id, created, money) ;

Unrelated to the issue, since usr_id is of integer datatype, you shouldn’t compare it with string literals. Instead of WHERE usr_id='0', use WHERE usr_id=0

Leave a Reply

Your email address will not be published. Required fields are marked *