Sfrgttk

What is the difference between `"..."`, `'...'`, `$'...'`, and `$"..."` quotes?

How do you funnel food off a cutting board?

The No-Straight Maze

How is this property called for mod?

Am I correct in stating that the study of topology is purely theoretical?

Does Skippy chunky peanut butter contain trans fat?

Non-Cancer terminal illness that can affect young (age 10-13) girls?

Stuck on a Geometry Puzzle

Why didn't the 2019 Oscars have a host?

Will rerolling initiative each round stop meta-gaming about initiative?

Website seeing my Facebook data?

How to not let the Identify spell spoil everything?

Critique vs nitpicking

Crack the bank account's password!

Has any human ever had the choice to leave Earth permanently?

Can a player sacrifice a creature after declaring that creature as blocker while taking lethal damage?

Boss asked me to sign a resignation paper without a date on it along with my new contract

Equivalent of "illegal" for violating civil law

Is there a verb that means to inject with poison?

Why does 0.-5 evaluate to -5?

Need help with a circuit diagram where the motor does not seem to have any connection to ground. Error with diagram? Or am i missing something?

What makes papers publishable in top-tier journals?

Calculate of total length of edges in Voronoi diagram

Which RAF squadrons and aircraft types took part in the bombing of Berlin on the 25th of August 1940?

MySQL GROUP BY query drastically slow down when after data reaches a certain amount

MySQL subquery slows down drastically, but they work fine independentlyMySQL query extremely slow after server restartWhy does MySQL slow down in loading data?Slow MySQL Query when OrderingLimiting query to certain amount of rows evenly distributedMySQL using cpu after cancelling slow queryMySQL Slows Down after MYD file reaches 2GB (64 Bit OS)Slow MySQL query with Group byMYSQL slow down using UNION ALLMySQL query suddenly slow down

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!

mysql

share|improve this question

asked 10 mins ago

stackerstacker

2425

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  5 mins ago
  

  
  
  
  

add a comment | 

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!

mysql

share|improve this question

asked 10 mins ago

stackerstacker

2425

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  5 mins ago
  

  
  
  
  

add a comment | 

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!

mysql

share|improve this question

asked 10 mins ago

stackerstacker

2425

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

;

share|improve this question

asked 10 mins ago

stackerstacker

2425

share|improve this question

asked 10 mins ago

stackerstacker

2425

asked 10 mins ago

stackerstacker

2425

asked 10 mins ago

stackerstacker

2425