Very light queries are in slow-query-log ! Why?

Question

I try to optimize my websites, because MySQL falled in timeout. I've simplied queries and added indexes. Server works fine, but in log stil some queries.

Amazing ! : the only queries on the slow-query-log are very simple and light queries. I don't understand !... Can you help me ?

My site is on Private MySql servor, OVH. Logs extract :

# Time: 170109 18:17:04
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.185552  Lock_time: 0.000117 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982224;
delete from gl_visiteurs 
                where IP like '68.180.230.33';
# Time: 170109 18:17:05
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 837382
# Query_time: 1.440233  Lock_time: 0.000151 Rows_sent: 0  Rows_examined: 0
SET timestamp=1483982225;
INSERT INTO gl_visiteurs (IP, user_id) 
                VALUES ('68.180.230.33',0);
# Time: 170109 18:17:20
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.92]  Id: 837412
# Query_time: 2.540715  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483982240;
delete from gl_visiteurs 
                where IP like '109.220.253.15';
# Time: 170109 18:40:36
# User@Host: doctsfgl[doctsfgl] @  [10.0.83.198]  Id: 839694
# Query_time: 1.566650  Lock_time: 0.000095 Rows_sent: 0  Rows_examined: 1
SET timestamp=1483983636;
delete from gl_visiteurs 
                where IP like '66.249.64.218';

These queries, when executed in PhpMyAdmin, run in very short laps, about 0.01 s !!! All columns concerned are indexed. Table contain less than 100 records, and 3 columns !...

An idea ? Many thanks (sorry for my english ; I'm french)

The create table statement:

CREATE TABLE IF NOT EXISTS gl_visiteurs 
( IP varchar(15) NOT NULL DEFAULT '', 
  start timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  logue int(11) NOT NULL DEFAULT '0', 
  user_id int(11) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Indices:

ALTER TABLE gl_visiteurs 
 ADD PRIMARY KEY (IP,user_id), 
 ADD KEY user_id (user_id), 
 ADD KEY start (start), 
 ADD KEY IP (IP); 

Answer 1

I noticed you are searching for an IP address

You could add an additional column with a numeric value for the IP

First, create the column to hold the numeric value of the IP and index it

ALTER TABLE gl_visiteurs ADD COLUMN IPVALUE INT UNSIGNED DEFAULT 0;

Next populate that new column and index it

UPDATE gl_visiteurs SET IPVALUE = INET_ATON(IP);
ALTER TABLE gl_visiteurs ADD INDEX (IPVALUE);

From there, you can do deletes via numeric lookups like this

delete from gl_visiteurs 
where IPVALUE = INET_ATON('68.180.230.33');

and do inserts like this

INSERT INTO gl_visiteurs (IP, IPVALUE, user_id) 
VALUES ('68.180.230.33',INET_ATON('68.180.230.33'),0);

UPDATE 2017-01-09 17:57 EST

Based on your comment, you can drop the IP column and keep the IPVALUE column

Then your inserts would be

INSERT INTO gl_visiteurs (IPVALUE, user_id) 
VALUES (INET_ATON('68.180.230.33'),0);

but to see the IP your selects must be something like

SELECT INET_NTOA(IPVALUE) as IP,... FROM gl_visiteurs

Answer 2

Here are several thoughts; none of which are definite answers:

Check to see if you have log_queries_not_using_indexes turned on. I find that to be virtually useless, and clutter the slowlog with unnecessary entries, such as you are finding.

What CHARACTER SET are you connected with? I see you are doing LIKE (unnecessarily) against a latin1 column. Use = if you are not using wild cards.

Please provide EXPLAIN ... for some of those queries; there may be some surprises.

So you have other queries touching the table? They could be interfering, thereby slowing down these queries.

Since the PRIMARY KEY starts with IP, KEY IP (IP) is redundant, and should be DROPped.

Since the PK is necessarily UNIQUE, you are limiting the entries in this table to be one per user per IP?

Provide SHOW ENGINE INNODB STATUS; some time when the problem occurs. It is likely to have some clues.

Are these statements part of a BEGIN...COMMIT transaction? If so, let's see more of the transaction. Note: If you are running with autocommit=0, you are in a transaction.

Do you really have a user_id=0, as indicated in your INSERT example?