Mysql – Very light queries are in slow-query-log ! Why

MySQLperformance

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);

Best Answer

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
Related Question