have you tried the EXPLAIN statement?

for example if you are using mysql...

MySQL :: MySQL 5.0 Reference Manual :: 8.2.1 Optimizing Queries with EXPLAIN:
http://dev.mysql.com/doc/refman/5.0/en/using-explain.html [dev.mysql.com]

I have not tried the explain statement but the main issue here
that there are 5.3 million rows (not 170.000 as i posted above).
Execution of query is under 1 second what is very fast and excellent but main problem is CPU. Anyone have idea from the code above what to do to keep same speed of execution but to cut down raise of CPU?

The EXPLAIN syntax is definitely what you need to be looking at and sharing (also, table format and # rows is useful... but a quick look at your query, it looks like it'd invariably be CPU intensive as you're:

1st query
- Evaluating 5.3 million records to find the unique values for r1
- Generating 5.3 million floating point random numbers with RAND()
- SELECT MAX(id) FROM visits) hopefully has an index on `id` or it will be slow

The 2nd query also has the additional REGEXP.

To start with try changing
$result=mysql_query($query);
to
$result=mysql_unbuffered_query($query);

And see if there's any improvement. See the manual page [php.net]

If the ORDER BY is not necessary try removing it.

Experiment to see whether any of your subqueries/functions are making the whole thing slower.

Sometimes it's also worth creating temporary tables to make these larger queries run more smoothly.

EXPLAIN syntax and output should provide some enlightenment.

It seems silly for a host to complain that your query is taking 100% of CPU for only a second (do you run the a lot?). Perhaps putting a small usleep(); in your while() loop will keep them happy.

Thank you for your reply. I've followed your advice and did changed:
$result=mysql_query($query);
to
$result=mysql_unbuffered_query($query);

I have also removed ORDER BY second query. By first query is needed to show up last 10 visits (where counter is 1) but by second query is randomly chosen and there is not needed.

Actually first code must show only last 10 visits and i have there replace old code:

$query=" SELECT * FROM visits WHERE counter='1' ORDER BY id DESC LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];

with new one (from the first post) but not sure of that was wise move?


If someone have another idea it will be more than appreciate

btw

What about mysql_unbuffered_query in future - "This extension is deprecated as of PHP 5.5.0, and will be removed in the future."?

[php.net...]

Thank you brotherhood of LAN for link to another option in future for deprecated mysql_unbuffered_query

Any idea of it was wise to change

$query=" SELECT * FROM visits WHERE counter='1' ORDER BY id DESC LIMIT 10";
$result=mysql_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];

into

$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_unbuffered_query($query);
while($row =mysql_fetch_array($result)){
$ip=$row['ip'];

echo "$ip,"; 

because first code is needed to show only last 10 visits (where counter is means = means they visited website for the first time) and nothing else.

Options for helping you are pretty limited until you post the info asked for.

- EXPLAIN of query
- Table structure

To be honest though, if you're dealing with a large number of records on a shared host, it's reasonable that a query would take 1 second. Figure out whether it's the query itself or the while() loop that's hogging CPU.

If it's the latter the only choice really is to slow the loop down by waiting with usleep();

Knowledge of MySql and what to do is very limited by my side. Actually i chose always to do everything without MySql if it is possible.
I can post here table structure:

Column Type Null Default Comments
id int(11) No 
page varchar(50) Yes NULL 
flag char(2) Yes NULL 
country varchar(50) Yes NULL 
abrev char(3) Yes NULL 
region varchar(50) Yes NULL 
city varchar(50) Yes NULL 
latitude double Yes NULL 
longitude double Yes NULL 
time varchar(20) Yes NULL 
ip varchar(100) Yes NULL 
counter int(11) Yes NULL 
url varchar(100) Yes NULL 

Inside MySql are inserted information about IP from visitor along with another information's (country, flag letters, latitude, langitude, region etc...).

Counter count how many time visitors have visited website.
Query from my examples above call only field ip in combination with counter (1 means to look only for visitors who have visited website one time).

Hope that you have something from this informations

To get the table structure:

SHOW CREATE TABLE tablename

Replace `tablename` with the name of your table.

To show the EXPLAIN info of a query just attach the word EXPLAIN to the start of the query and run the query

Sound easy but because of limited knowledge with MySql not sure exactly what to do here.

Table structure is already posted above if it is what is needed but with EXPLAIN in front of a query and where to execute i'm a bit lost.

Can you log into MySQL via the command line, or a GUI interface like phpMyAdmin?

I have access to phpMyAdmin. You mean go there and then under SQL execute query such as

SHOW CREATE TABLE visits ?

yes - after you do this and report the results:

SHOW CREATE TABLE visits;

do this and report the results:

EXPLAIN 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;

then do this and report the results:

EXPLAIN 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;

For this:

SHOW CREATE TABLE visits;

i got following result:

Table Create Table
visits CREATE TABLE `visits` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `page` varchar(50) DEFAULT NULL,
 `flag` char(2) DEFAULT NULL,
 `country` varchar(50) DEFAULT NULL,
 `abrev` char(3) DEFAULT NULL,
 `region` varchar(50) DEFAULT NULL,
 `city` varchar(50) DEFAULT NULL,
 `latitude` double DEFAULT NULL,
 `longitude` double DEFAULT NULL,
 `time` varchar(20) DEFAULT NULL,
 `ip` varchar(100) DEFAULT NULL,
 `counter` int(11) DEFAULT NULL,
 `url` varchar(100) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5344957 DEFAULT CHARSET=latin1

For this

EXPLAIN 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;

i got following result:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULLNULLNULLNULL1 Using temporary
1 PRIMARY r1 range PRIMARY PRIMARY 4 NULL4588549 Using where
2 DERIVED NULLNULLNULLNULLNULLNULLNULLNo tables used
3 SUBQUERY NULLNULLNULLNULLNULLNULLNULLSelect tables optimized away

For this one:

EXPLAIN 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;

i received following result:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULLNULLNULLNULL1 Using temporary
1 PRIMARY r1 range PRIMARY PRIMARY 4 NULL3008459 Using where
2 DERIVED NULLNULLNULLNULLNULLNULLNULLNo tables used
3 SUBQUERY NULLNULLNULLNULLNULLNULLNULLSelect tables optimized away

for starters i would try creating an index for the 'ip' and 'counter' columns.

then you might consider using the alphanumeric character class within a bracket expression in your second SELECT statement.
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp [dev.mysql.com]:

...
AND ip REGEXP '[[:alnum:]]' ...

while i haven't tested this i would assume that it's faster than "... AND ip REGEXP '[A-Za-z0-9]' ..." so it's probably worth a try.

thank you. I've made changes to REGEXP and replaced with '[[:alnum:]]'

Regarding creating an index for the 'ip' and 'counter' columns i will need to read a bit around to find how to update existed 'ip' and 'counter' column with an index.

Is there need to change anything else except creating an index for the 'ip' and 'counter' columns?

btw

Regarding creating an index for the 'ip' and 'counter'. Is this right query that need to be exexuted within phpMyadmin for creating index:

ALTER TABLE visit ADD INDEX(ip); 
ALTER TABLE visit ADD INDEX(counter); 

simply creating the indexes should provide significant improvement.
try it and test the results to see where you stand.

MySQL :: MySQL 5.1 Reference Manual :: 13.1.13 CREATE INDEX Syntax:
http://dev.mysql.com/doc/refman/5.1/en/create-index.html [dev.mysql.com]

I;ve created index through phpMyadmin. There is already option on right side to 'add index' with simple clicking on 'add index'. (i hope that it is same instead of running query to add index to columns 'ip' and 'counter')

I see directly improvement in speed. Before was under 1second and now is 2x faster.

Great and thanks again. Your input to help me was excellent.

I will monitor through Cpanel (Resources Usage) of CPU raising to 100% has been dropped.

Sorry that i come back here again:

EXPLAIN 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;

I have doubt about this code because code is optimized to chose rand from MySql table but i need only to chose last 10 records. I think that here is well some time lost because of (SELECT ROUND(
RAND( ) * (

I'm starter and do not know a lot. Just have doubt about this. Any idea about this and about optimized query for the last records.