berna85

Hi,
thank you very much for your answer.

The query seems to works fine, it takes 33,5 milliseconds to be executed.

But my problem is when query use also the last_counter filter in the where clause.
From my slow queries log:

# Time: 221110  0:01:02
# User@Host: ...
# Thread_id: ...
# Query_time: Query_time: 6.819971  Lock_time: 0.000052  Rows_sent: 1 Rows_examined: 2367179
# Rows_affected: 1
SET timestamp=1668034862;
SELECT location FROM wp_statistics_visitor WHERE ip = '<ip_address>' and last_counter >= '2021-11-10' ORDER BY ID DESC LIMIT 1;

Testing this query in the PHPMyAdmin it takes about 4,70 seconds to be executed.

SELECT location FROM wp_statistics_visitor WHERE ip = '<ip_address>' and last_counter >= '2021-11-10' ORDER BY ID DESC LIMIT 1;

If I can suggest, the following query give me the same result in about 33,5 milliseconds.

SELECT location 
FROM wp_statistics_visitor main 
INNER JOIN (
   SELECT MAX(ID) as idd, ip, last_counter  
   FROM wp_statistics_visitor 
   WHERE ip = '<ip_address>' 
   HAVING last_counter >= '2021-11-10' 
) sub on sub.idd = main.id;

Best Regards

• This reply was modified 3 years, 7 months ago by berna85.
• This reply was modified 3 years, 7 months ago by berna85.
• This reply was modified 3 years, 7 months ago by berna85.