Long Query

Hi All

I have the following query (a select then an update within a while loop) that takes over an hour to complete, the only other thing I have in this php script is the DB connect.

$result = mysql_query(“select * from domain ORDER BY at ASC LIMIT 0, 950”);
while ($row = mysql_fetch_array($result))
{
$myDomains = $row[domain];
mysql_query("update domain set at=now(), session=‘$newid’ WHERE domain = ‘$myDomains’ ");

}

What am I doing wrong? Is there a simpler way I can do this?

All I am trying to do is select the 950 oldest records by their timestamp and then prepare them for the rest of my script to work on i.e. reserve those records for the rest of my script.

Reason: I have multiple copies of the same scripts running on several servers all updating the oldest records, I don’t want them to trip over each other

Thanks for any help

Thought I had it… but no :frowning:

Haven’t tested this but you should be able to do it in a single sql statement

update domain set `at`=now(), `session`='$newid' WHERE domain in (select domain from domain ORDER BY at ASC LIMIT 0, 950)

HI PhilipToop thanks for the reply

That is exactly where I started but you cant have ‘LIMIT’ in a subquery for some reason

/* SQL Error (1235): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’ */

Using MYSQL Version:

mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (i686) using readline 6.2

How about you do the query like you where before in order to get the ‘at’ value for the oldest entry you want to change. Then use that in WHERE clause of you update statement.

Again I haven’t tested but how about

UPDATE domain left join (SELECT DOMAIN FROM DOMAIN ORDER BY at ASC LIMIT 0, 950) t on t.domain = domain
set `at`=now(), `session`='$newid' 
where t.domain is not null

that’s not the mysql server version

run this query –

SELECT VERSION()

i’m guessing your version is old, and you should probably upgrade

Select Version () gives:

5.1.58-1ubuntu1

The Query:

UPDATE domain LEFT JOIN (SELECT domain FROM domain ORDER BY at ASC LIMIT 0, 950) t ON t.domain = domain.domain
SET at=now(), session=‘11111’
WHERE t.domain IS NOT NULL

took: 1,393.853 sec

root@virtualdev:/var/www/domain# apt-get install mysql-server
Reading package lists… Done
Building dependency tree
Reading state information… Done
mysql-server is already the newest version.
0 upgraded, 0 newly installed, 0 to remove and 3 not upgraded.

took: 1,393.853 sec

Is the field ‘domain’ indexed?
How many records in your database?

Did my reply

“How about you do the query like you where before in order to get the ‘at’ value for the oldest entry you want to change. Then use that in WHERE clause of you update statement.”

offer you an alternative approach?

Hi

I tried your suggestion but it was still giving times of 30 mins for updating 950 records, in the end I have done this…

UPDATE domain SET session=‘$newid’
WHERE id IN (
SELECT id FROM (
SELECT id FROM domain
ORDER BY at ASC
LIMIT 0, 950
) tmp
)

Rightly or wrongly, but this brings it down to 8 seconds which although slow is acceptable as this is only ever going to run on a cron job so no-one will be sat waiting for a page load.

I have the id and domain indexed, I have played with removing indexes, and adding indexes to other columns such as at but it makes little or no difference, also tried increasing mysql buffer sizes but again with little result.

Thanks for all the help!

It would be interesting to know how long

UPDATE domain LEFT JOIN (SELECT id FROM domain ORDER BY at ASC LIMIT 0, 950) t ON t.id = domain.id
SET `at`=now(), `session`='11111' 
WHERE t.id IS NOT NULL

Might have taken.

The revised update query is a nice work around for not being able to include the LIMIT within a subquery using the IN operator.

I tried your suggestion

Was this the use of ‘at’ in the where clause of the update?
Was ‘at’ indexed at the time?

Just tried your query… Whooop! down to a more than respectable 0.141

Your a Star!!!

I am sure I had something similar yesterday perhaps it is an index issue after all?

With the ‘id’ you are probably talking about a fixed length field of 4 bytes. With a domain name your probably talking about a variable length field with considerably more bytes.

One question … Is the field domain unique?

If it isn’t you will be potentially updating more records originally using the domain field as opposed to using the id field.

Yes the domian is uniqie although I have not forced that at DB level, would that have a speed impact?

I have jigged it around a bit and could now use the ID which as you say would be better

would that have a speed impact?

No.

Provided the index for domain was in place the speed difference must be simply down to transferring an index of integers rather than variable length. Although I am surprised at the size of the difference.