One query cause 100% CPU

Actually i'm not sure what to do here. I'm using shared hosting and just received message from hoster that my MySql code, actually each query make 100% CPU. Thus if there are 3 queries then it is 300% CPU. Strange.​

Code that i used is well optimized for fast performance:​

$query=" SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND counter='1'
ORDER BY r1.id DESC
LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];
 
echo "$ip,"; 

PHP:

Another piece of code is almost similar:

$query=" SELECT DISTINCT ip FROM visits AS r1
JOIN (SELECT ROUND(
RAND( ) * (
SELECT MAX( id ) FROM visits)
) AS id
) AS r2
WHERE r1.id >= r2.id
AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'
ORDER BY r1.id ASC
LIMIT 8";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){

$ip=$row['ip'];
echo "$ip,";

PHP:

Is there anything wrong with code? Any correction to making not raise of CPU for 100% by each query​

btw​

Table is very big. There are 5.3 million rows as it means anything.​

Is possible that large table can be also reason for raising of CPU 100%?

Thanks for any input.​

 

Just to add here that execution of query is very fast, under 1 second but can someone help with the code above to do something to cut down raise of CPU.

 

First, I'd try a test without the REGEXP part and see how the CPU load will change.
(As a test, just remove that line 9 from example #2 and execute the query).

In some cases REGEXP is very expensive to use (in terms of CPU time).

 

Thank you for your input but what should i use instead REGEXP?

AND ip REGEXP '[A-Za-z0-9]' AND ip NOT REGEXP '\\([^\\)]*www.*\\)'

PHP:

Second query was made to chose randomly rows with numbers/letters.

I've already on advice from someone else removed ORDER BY r1.id ASD in second code because it is not needed and changed
$result=mysql_query($query);
to
$result=mysql_unbuffered_query($query);


Any other input to cut down raise of CPU and keep same speed of execution is more than appreciate

 

I mean, you first need to find out whether this very part of the query causes the trouble or not. If you remove the 'regexp' line, but the problem persists - search in the other parts of the code.

The 'order by' part indeed seems obsolete here, however it works very fast and thouldn't be the problem.

 

You are right Garkoni but it is a bit annoying here because i have not access to logs of resources so that i after removing one line can see directly results. I should need here to mail support each time and ask "Can you please see now of CPU is lower" And it is a bit annoying.

I would rather from available information's see what is possible to do it from my side without bothering support each time of they can see it and let me know.

 

Your nested join JOIN (SELECT ROUND( is going to add a ton of overhead as well. If you have limited resources you may need to completely avoid using nested JOINS and possibly SELECTS.

If you're dealing with storing ip addresses, I would definitely use INET_ATON() and INET_NTOA() functions and store an ip address as an integer.

Also, I'm not sure how your host is setup, but you shouldn't be able to max a CPU at 100% on a shared server. This means that anyone else on the server can do the same. Not a well planned setup if this is the case. It's virtually impossible to plan an application with a specific non-limited CPU or memory usage.

 

it's because of "order by". order by clause always cause cpu usage. So if you can order data on server side it's better to remove on query.

 

Please try replacing

NOT REGEXP '\\([^\\)]*www.*\\)'

Code (markup):

with

not like '%www%' 

Code (markup):

and add index in ip field