mysql - Addition of COUNT to query causes query to get stuck -


edit: simplifying it's trivial example:

select count(*) a_table use index(contract) left join b_table on a_table.contract = b_table.contract 

also, nothing. explain:

enter image description here

i have 2 queries identical (b_table temporary table):

select if (b.contract null, 1, null) c a_table left join (select contract b_table) b on a.contract = b.contract 

this query returns reliably in below .25 seconds. here explain:

enter image description here

here same query, count() around if:

select count(if (b.contract null, 1, null)) c a_table left join (select contract b_table) b on a.contract = b.contract 

this 1 shows 'sending data' , @ least after several minutes, seems hang , nothing. here explain:

enter image description here

(note d == a_table a , eric_tmp == b_table. not confusing.)

this original query caused kerfuffle, above attempt rewrite it:

select count(*) a_table not exists (select 1 b_table b a.contract = b.contract) 

which of course hung. heck going on here? i've tried adding , using non-unique index, doesn't seem help. don't understand why count cause problem here.

you consider first query done when returns first row, rather last row. second query needs go through data, before can return anything.

subqueries (which mysql calls "derived tables") affect performance. negatively.

your not exists query quite reasonable:

select count(*) a_table not exists (select 1 b_table b a.contract = b.contract); 

for performance, want index on b_table(contract). fix performance problem, unless tables big.


Comments

Popular posts from this blog

c++ - Difference between pre and post decrement in recursive function argument -

php - Nothing but 'run(); ' when browsing to my local project, how do I fix this? -

php - How can I echo out this array? -