1

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); 
7
  • Is there a reason why you're using like instead of = ? If you can always expect the system to store complete IP addresses, then there's no reason to have the like operator.
    – Lizzy
    Jan 9, 2017 at 18:20
  • Show us the SHOW CREATE TABLE gl_visiteurs; output. Jan 9, 2017 at 18:22
  • No reason. I usually do that, maybe it is not the best way. I'll replace with '='
    – JMB
    Jan 9, 2017 at 18:34
  • 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;
    – JMB
    Jan 9, 2017 at 18:37
  • You either changed the output or the table has no indexes at all. Jan 9, 2017 at 18:45

2 Answers 2

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
3
  • Thanks, I will try. Inet_aton is new for me ! Great, thanks !
    – JMB
    Jan 9, 2017 at 20:05
  • 1
    OK, done. But in insert statment, why keep "IP" ? Redondant with IPVALUE, and no more usefull ?
    – JMB
    Jan 9, 2017 at 20:10
  • Hello, idem : these 2 queries (delete and insert) take 1,5s via PHP, and only 0.01 via phpMyAdmin. A great great mystery !...
    – JMB
    Jan 9, 2017 at 21:52
0

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?

2
  • Many thanks for the help. I cannot enable log_queries_not_using_indexes : "set global log_queries_not_using_indexes = 'ON'" give "Access denied; you need (at least one of) the SUPER privilege(s) for this operation". I'm on shared servor, (OVH mutualisé) Character set is actually UTF8 ini_set( 'default_charset', 'UTF-8' ); I just change latin1 to Utf8_general_CI I already changed "like" with "=" Explain on insert query give : id 1 Select type SIMPLE 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
    – JMB
    Jan 11, 2017 at 13:21
  • Sorry for the line breaks !... "End a line with two spaces to add a <br/> linebreak:" do not effect !??? No other queries touching the table. OK I delete redundant IP key I've not enough privilege for SHOW ENGINE INNODB STATUS; No transaction Yes, I have user_id =0 users : not logged users
    – JMB
    Jan 11, 2017 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.