COUNT, SUM, ORDER and 2xJOINS

MySQL & MariaDB Other MySQL® Questions
1

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