Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Synthax help

  1. #1
    Join Date
    Oct 2009
    Posts
    11

    Question Unanswered: Synthax help

    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?

  2. #2
    Join Date
    Sep 2009
    Posts
    64
    Assuming that there may be agents that doesn't have clients, here's how you'd do it:

    Code:
    SELECT
    	agents.id, agents.name,
    	IFNULL(SUM(clients.quantity), 0) AS sales
    FROM
    	agents
    	LEFT JOIN clients ON clients.source - agents.id
    GROUP BY
    	agents.id, agents.name
    ;

  3. #3
    Join Date
    Sep 2009
    Posts
    64
    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.

  4. #4
    Join Date
    Oct 2009
    Posts
    11
    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.

  5. #5
    Join Date
    Oct 2009
    Posts
    8
    Code:
    SELECT
       c2.id,c2.name,clts.id,clts.name,sum(clts.quantity)
    FROM
       clients clts
       JOIN (
          SELECT
             SUM(c.quantity) AS quantity, c.source,c.quantity,c.name,c.id,agents.name
         FROM
             clients c
             JOIN agents ON agents.id = c.source
         GROUP BY
             agents.id
       ) AS c2 ON clts.source = c2.id
           AND clts.quantity = c2.quantity
           AND clts.quantity > 0
    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.

  6. #6
    Join Date
    Sep 2009
    Posts
    64
    Quote Originally Posted by Francisc
    I had to replace the "-" with "=", but other than that the results are the expected ones in terms of data.
    oh yeah. That's a typo.

    Quote Originally Posted by Francisc
    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?
    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.

  7. #7
    Join Date
    Oct 2009
    Posts
    11
    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.
    Last edited by Francisc; 10-13-09 at 07:52.

  8. #8
    Join Date
    Oct 2009
    Posts
    11
    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 | |
    +----+-------------+---------+------+---------------+------+---------+------+-------+----------------------------------------------+

  9. #9
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    as was said early on in post create index on the agents id in the client table.
    Dave

  10. #10
    Join Date
    Oct 2009
    Posts
    11
    Ok, so I should create indexes on the ID columns for both the agents and the clients table?

  11. #11
    Join Date
    Oct 2009
    Posts
    11
    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?

  12. #12
    Join Date
    Sep 2009
    Posts
    64
    :-) See my earlier reply

    Quote Originally Posted by mnirwan
    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.

  13. #13
    Join Date
    Oct 2009
    Posts
    11
    I must have missed that. Sorry!

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

  14. #14
    Join Date
    Sep 2009
    Posts
    64
    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.

  15. #15
    Join Date
    Oct 2009
    Posts
    11
    Right! I'll remember that!

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •