Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.


mbormbor EntrantCurrent User Role Beginner

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 </pre>

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 <derived2> ALL NULL NULL NULL NULL 2 1 PRIMARY <derived3> 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</pre>

without ORDER BY everything is good, 0.27s


  • teajay2teajay2 Entrant Current User Role Beginner

    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 =
    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.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.