When I passed it through

pr0gr4mm3r — Sat, 01 Nov 2008 01:19:05 +0000

When I passed it through phpMyAdmin, it had a script timeout at 300 seconds into the query run, but when I ran it at the command line, it only took 5 or 10 seconds. I'm not sure why phpMyAdmin had such a problem.

Do you remember how long

teammatt3 — Fri, 31 Oct 2008 23:49:36 +0000

Do you remember how long that query took to run? Was it in the minutes? I'm just curious...

Yup, that worked. I

pr0gr4mm3r — Fri, 31 Oct 2008 03:29:32 +0000

Yup, that worked.

I previously ran an alternate query where I ran the internal one from the one I posted above, took the returned IDs, and placed it in the place of the subquery, so it was something like:

UPDATE products SET products_price =3 * products_price WHERE products.products_id IN (
*insert 40,000 IDs here*
) 

phpMyAdmin couldn't run it because of the size alone. I had to save it in a SQL file, and import it on the good 'ol command line. Your query is more efficient.

This is just a hunch, but I

teammatt3 — Thu, 30 Oct 2008 23:49:00 +0000

This is just a hunch, but I remember reading that MySQL treats sub queries in the WHERE clause different from those in the FROM clause. (I could totally be making that up though )

Can you rewrite the query to use the subquery in the FROM clause? You can use an AND to kind of emulate the WHERE.

*Don't run this in a production environment*
[code]
UPDATE
products
INNER JOIN
products_to_categories ON products.products_id = products_to_categories.products_id AND products_to_categories.categories_id = 4
SET
products_price = 3 * products_price
[/code]

Does that work?

I can answer part of your

JeevesBond — Thu, 30 Oct 2008 15:15:44 +0000

I can answer part of your question:

Why doesn't it get all the results from the sub query and then feed it all at once into the parent query?

Because MySQL's handling of subqueries are rubbish. It treats every subquery as a correlated subquery, meaning for every result row of the outer query the inner query is run again.

Your best bet is to create a temp table, then SELECT from that.

Postscript: man, I've got to fix those code boxes.