Hello!

For this structure:
Table 1 - agents:
id - primary, auto incremental
name - name of agent

Table 2 - clients:
id - primary, auto incremental
name - name of client
quantity - int, quantity of products client purchased
source - int, the id correspondent from Table 1

Now, what I need is the show Table 1 with an aditional column "sales" which is the sum of the quantity column where the source is the same.

Note 1: the correspondace between Table 1 and Table 2 is provided by agents.id=clients.source.
Note2: not all the agents sold to clients, so the sales column should say 0 for them - reason for which I need a JOIN operation instead of the easier WHERE.

This query takes a huge ammount of time to complete for lots of data:
select agents.*,(select sum(clients.quantity) from clients where clients.source=agents.id) as sales from agents;

How can I speed it up, please?

Assuming that there may be agents that doesn't have clients, here's how you'd do it:

One reason why your original query takes a long time is that for MySQL will have to run the sub query for each agent_id. If you have 1000 agent_id, then the sub query will be run 1000 times. If you don't have index on the agent_id field on clients table, then each of the 1000 queries will do full table scan.

You may be interested in this article as well for such optimizations: Microshell Optimizing SQL that selects the max/min/etc from a group.

I had to replace the "-" with "=", but other than that the results are the expected ones in terms of data.

Problem is still the same though... the speed is very slow.
The agents and clients table have some tens of thousands of records.

How can I make it at least acceptably fast?
Thank you for the article you linked, I will give it a read this week.

Have not been tested. This will point you in the right direction. Will not list the agents that have no client sales. Base on the article given by mnirwan. Good luck.

oh yeah. That's a typo.

check the indexes. If possible, paste the EXPLAIN result here. If you dont know what it means, add explain at the beginning of query. Or paste the table def.

Thanks a lot for your interest in helping me.

I need the agents that do not have clients as well.
If I pasted the table definitions there would be a problem because they are not in English and it would be hard to understand and secondly the 2 tables I am using are hard to understand without some other 3 or 4 tables.

The example I gave was an analogy.
However, I have no indexes on any of the columns, I only have the ID columns for both the agents and the clients set to primary.

There is the explain reply:

+----+-------------+---------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------------------------------------+
| 1 | SIMPLE | agents | ALL | NULL | NULL | NULL | NULL | 23570 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | clients | ALL | NULL | NULL | NULL | NULL | 8472 | |
+----+-------------+---------+------+---------------+------+---------+------+-------+----------------------------------------------+

as was said early on in post create index on the agents id in the client table.
Dave

Ok, so I should create indexes on the ID columns for both the agents and the clients table?

YES! Thank you very much!
What I needed was to index the agents ID coulumn in the clients table combined with the SQL query above!

Greatly appreciate both your help!
Is there a good article on SQL indexes - when and where to add them?

:-) See my earlier reply

I must have missed that. Sorry!

The site on your signature has any articles about when and where to add indexes? Thank you!

No it doesn't (yet). I may do it in the future though.

For starters, however, try to do "EXPLAIN" on the queries that you're planning to run. That should be a good start. Also check on the fields commonly used in the "WHERE" clause. Don't go overboard and start indexing all the columns as it's basically similar to the sayings "Jack of all Trades, Master of Nothing". If you index all the columns, it's pretty much nullifies the advantage of having index.

Right! I'll remember that!

Would you say it is generally ok to index columns that reffer to IDs of elements from other tables?