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);
SHOW CREATE TABLE gl_visiteurs;
output.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;