SELECT 
COUNT (0) 
The FROM 
T_orh_apply 
The WHERE (
Does=FALSE 
AND o_type IN (' 5 ', '2', '6') 
AND c_code='5020' 
AND (
(a_id IN ()) - parameters within 100 article 
OR (c_id IN ()) - another table parameters about 1200 largest data correlation t_c 
OR (e_id IN ()), probably around 25000 data correlation parameters another table t_e 
OR (w_id IN ()) - parameters within 100 article 
) 
); 

This SQL query is relatively slow and about 8 s, the main table data (t_orh_apply) 534456, t_c: 1200, t_e: 25000
C_id IN () and e_id () statement if IN use after the subquery can go index, but id set associated SQL query out this relatively complex cannot draw into a SQL

Strives for the big show what optimized solutions

CodePudding user response:

OR into a union all
Four id indexed
C_id in and e_id in changing inner join, the t_c and t_e as driver table (left)

CodePudding user response:

 SELECT 
COUNT (*) 
The FROM 
T_orh_apply AS t 
LEFT the JOIN t_c AS c ON tc _id=c.i d 
LEFT the JOIN t_e AS e ON t.e _id=e.i d 
LEFT the JOIN t_w AS w ON t.w _id=w.i d 
WHERE t.d eleted=FALSE AND t.o _type (IN '5', '2', '6') AND tc _code='5020' 
AND c.i d (IN) AND e.i d () IN the w.i AND d IN () 

CodePudding user response:

A_id, c_id, e_id w_id, plus index respectively, is to add four indexes, not one index four fields,

SELECT (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND a_id IN ()) + (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND c_id IN ()) +
(SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND e_id IN ()) + (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND w_id IN ())
The FROM DUAL;

CodePudding user response:

reference AHUA1001 reply: 3/f

a_id, c_id, e_id, w_id, plus index respectively, is to add four indexes, not one index four fields,

SELECT (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND a_id IN ()) + (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND c_id IN ()) +
(SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND e_id IN ()) + (SELECT COUNT (0)
The FROM t_orh_apply
WHERE does=FALSE
AND o_type IN (' 5 ', '2', '6')
AND c_code='5020'
AND w_id IN ())
The FROM DUAL;

Into this rather slow, and may not to repeat the
Go two indexes a_id, w_id

CodePudding user response:

reference 1/f, the heavy rain will respond to:

OR into a union all
Four id indexed
C_id in and e_id change in inner join, the t_c and t_e as driver table (left)

E_id have null values can not inner, the original four id has a single index

CodePudding user response:

In content to establish a temporary table, to be associated with physical table fields indexed, then do associated query, expand innodb_join_buffer_size value, expand your read_buffer_size, read_rnd_buffer_size, max_length_for_sort_data, innodb_buffer_pool_siz

CodePudding user response:

Both tmp_table_size expand