Query critical issue
-
Wrong query getUserCountryFromDB on wp_statistics_visitor table. The records in this table grow rapidly and the performance of the query deteriorates more and more util the select statement reads millions of records to extract the result and the mysql server overloads the cpu.
Sort the ids and limits the results to 1 to extract the location with the max id is a bad practice causes severe performances issues and does not take advantages of indexes. Please consider use of a subquery returning the max id with only ip in where clause and last_counter filtered with the having statement, then join the founded id with wp_statistics_visitor table itself to retrieve the location value. In this way the query has constant execution performance.
-
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
The topic ‘Query critical issue’ is closed to new replies.
