Announcement

Announcement Module
Collapse
No announcement yet.

COUNT, SUM, ORDER and 2xJOINS

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • COUNT, SUM, ORDER and 2xJOINS

    hi,

    I'm noob but still lerning so can you help me with this?

    I have res_item table with articles (about 200k rows), fa_user table with authors, notes (users can vote for articles, note is sum( no_note ) and comments table (I want to show number of comments count( co_id )).
    Aricles have to be ordered by it_id DESC.

    My query:

    SELECT i . * , note_sum, comments_count, u.user_nameFROM (`res_item` i)INNER JOIN `fa_user` u ON i.it_us_id = u.idLEFT JOIN (SELECT no_it_id, sum( no_note ) AS note_sumFROM notesGROUP BY no_it_id)n ON n.no_it_id = i.it_idLEFT JOIN (SELECT co_it_id, count( co_id ) AS comments_countFROM commentsGROUP BY co_it_id)co ON co.co_it_id = i.it_idORDER BY it_id DESCLIMIT 10


    92.0550 s (

    The query EXPLAIN

    id select_type table type possible_keys key key_len ref rows Extra1 PRIMARY i ALL NULL NULL NULL NULL 182850 Using temporary; Using filesort1 PRIMARY u eq_ref PRIMARY PRIMARY 4 podajcegle_2.i.it_us_id 1 Using where1 PRIMARY ALL NULL NULL NULL NULL 2 1 PRIMARY ALL NULL NULL NULL NULL 1 3 DERIVED comments ALL NULL NULL NULL NULL 4 Using temporary; Using filesort2 DERIVED notes ALL NULL NULL NULL NULL 2 Using temporary; Using filesort


    without ORDER BY everything is good, 0.27s

  • #2
    Hey,

    Since you are only looking to return 10 queries, it might be better to do a subquery rather then the joins.

    For example:


    select i.*, u.user_name,
    (select sum(1) from notes n where n.no_it_id = i.it_id) as note_sum,
    (select count(1) from comments c where c.co_it_id = i.it_id) as comments_count
    from res_item i
    join fa_user u on i.it_us_id = u.id
    order by i.it_id desc


    In addition, you might wanna add indexes, such as on the following columns:
    notes - no_it_id
    comments - co_it_id
    fa_user - it_us_id

    You can play with adding an index to 'it_id' on the res_item table, but I don't think its going to do much since its not used in the where clause anywhere.

    -T

    Comment

    Working...
    X