Shell Programming and Scripting

I guess I don't understand ...
I mean, at this stage:

change it to:

The last process:
o INSERT or UPDATE the table files (with latest mod/owner) that was my problem and the reason why I started this thread
o INSERT into the table versions
o DELETE from table uploaded

I guessed right
My idea was: if you have this information earlier (status 1 and 2),
just propagate it as soon as you can.

Currently, I run 2 queries per record and 24 general queries.

Are you telling me I should process lines one by one for every operation which means running 26 queries per record?

With an average of 50 files received per minutes, do you confirm I'd better run
6,500 queries every 5 minutes
rather than
524 queries every 5 minutes?

No,
or better still, it depends.

I thought that you were processing the data record by record and at the end you were mass updating the modification time in the files table.

If the modification time update cannot be placed in those one-by-one queries, it's probably better to avoid the approach I suggested.

But as I said it depends.

Initially the problem was the wrong operation (because of the partial group by supported by MySQL), after that the statement was fixed.

Now it's about performance.

If the elapsed of the massive update (single statement -> many rows) is critical, even doing more transactions at a different time (row-by-row, 100 statements for 100 rows) could make sense.

Hope this helps.

Time is indeed an issue given the fast growing number of uploaded files.
I isolated one operation: retrieve the file id on 300,000 records.
With a massive update, it takes about 75 seconds.
With a one by one update, I started clocking more than an hour ago and it's not even finished.

Anyway, thank you very much for helping me go deeper in the structure of my database. It was very interesting.

Cheers
Santiago

---------- Post updated at 15:11 ---------- Previous update was at 15:06 ----------

Usually there's a way to reward people who help with bits.
I can't find the button anymore. Do you know where it is?

---------- Post updated at 15:24 ---------- Previous update was at 15:11 ----------

I stopped the query because I needed my CPU ;o)
Trying to extrapolate the number of records already processed, I evaluated that the one by one update is around 2,000 times longer than the massive update.

---------- Post updated at 15:26 ---------- Previous update was at 15:24 ----------

I forgot to mention that I think the incredible difference is certainly due to the well chosen indexes you told me to create.

Just read carefully what I said:

Or maybe, many transactions spread over a larger period.

It's not about one block of one-by-one vs a massive one ...