Working on an app that requires a very large amount of data transferred from the DB. It includes SELECT and UPDATE queries both (i.e. read and write to the DB).
First I need to get a list of all the products (approx. 1000 SKUs). The SELECT query is very simple:
SELECT
*
FROM
`my_db`.`products`
Then some automatic updates are done to each product. The UPDATE query is something like this:
UPDATE
`my_db`.`products`
SET
`updated` = NOW(),
`mods` = `mods` + 1,
/* a couple more updated fields here, if needed */
Now the question is which one would be faster/better to perform: a single query that manipulates with all the records at once, or a PHP loop (e.g. foreach or while) that works with each product separately (1000 single-row queries)? The latter would require a WHERE model = 'ABC' and LIMIT 1, of course.
To make the matter more complex (and comprehensive), think of other affecting criteria, e.g.:
- What if a different number of rows was targeted: 100 or 100,000?
- What if more than one table was involved? See an example of a JOINed query below.
.
SELECT
*
FROM
`my_db`.`table1` AS `t1`
INNER JOIN `my_db`.`table2` AS `t2` ON `t1`.`id` = `t2`.`fk`
INNER JOIN `my_db`.`table3` AS `t3` ON `t2`.`id` = `t3`.`fk`
/* Etc. */
Is there a general "rule of thumb" to decide which way is better in this or that situation?
