0

I need help finding out why the following query that aggregates peoples' responses by "item" slows down considerably after the data being aggregated reaches a certain size.

select
  t1.item,
  count(t1.val) as count,
  round(avg(t1.resp), 2) as mean,
  round(std(t1.resp), 2) as std
from
  person_response as t1
  join person_info as t2
    on t1.person_id = t2.id
where
  t1.resp between 1 and 5
  and t2.is_valid = 1
  and (t2.attr1, t2.attr2) in ((?, ?), ...)
  and t2.attr3 in (?, ...)
  and t1.item in (?, ...)
group by t1.item
;

t1 has about 15 million rows and t2 has about 200,000 rows. All columns involved in the query are indexed. If the WHERE clause captures under 50,000 rows, execution is generally under 100 ms. If the WHERE clause captures 138,665 rows, execution is under 500 ms. But when I extend the WHERE clause to capture 150,685 rows, execution takes considerably longer at 3 m 40 s 366 ms. Nearly 4 minutes!

closed as too broad by Rick James, mustaccio, Gaius, Marco, hot2use Mar 13 at 10:19

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Please provide SHOW CREATE TABLE t1 and SHOW CREATE TABLE t2 outputs. "All columns involved in the query are indexed." That statement doesn't mean much if they are just single column indexes. – Willem Renzema Feb 26 at 0:30
  • 1
    Please consider reading this advice – mustaccio Mar 7 at 13:21
1

I solved the problem. I needed a single-column index on t1.item. It had a multi-column index that was doing nothing for the query.

Not the answer you're looking for? Browse other questions tagged or ask your own question.