Optimize query

[jaymc]

I have a cron job execute a script every 1 minute

 

That script queries a table and drags out 15,000 rows, and for each row then executes another query on updating a table

 

So thats basically 15,000 queries..

 

Its killing the server when it runs and locking mysql quite heavily

 

Rather than have 15,000 seperate queries I was think of using 1 query and structuring it as so

 

WHERE IN ID (1,2,3,4,5,6,7.......15,000)

 

A whopping single query, but could it cope with 15,000 'IN IDs' and would it be better than just having 15,000 single queries

[fenway]

15K queries is always going to be slower... yes, an IN clause with 15K uids won't be ideal (though index merge in 5.0 might handle it better), but that's much preferred.

[shoz]

You may be able to use a single query to do the UPDATE (ie not having to do the first SELECT), but you'd have to explain what the UPDATE is doing in more detail. A code snippet should suffice.

[jaymc]

What if I use the in ID method with 700 entreies rather than 15,000

 

Would that be fine?

 

Ive actually thought of a way to reduce the rows your see

 

So basically

 

WHERE IN ID(1,2,3,4,5,6,7,8....700)

[jaymc]

Edit:

 

Whats better

 

700 queries or an IN ID with 700 ID's

[Vikas Jayna]

How about using a subquery:-

 

WHERE ID IN (SELECT ID FROM TABLENAME WHERE blah blah ...)

 

A single query like this is certainly going to be better (provided the column ID is indexed)

[fenway]

That's still using an IN clause, so it's about the same.