CODEWITHSUNDEEP
phpmysqljoin
uzor
uzor

Reputation: 89

mysql query optimization with join and subquery

Can someone pls help with this. From the slow log This query below takes 11 seconds to run and its eating up server resources. How do i re-write this query to achieve greater optimization?

P.S: The tables are indexed.

The query : 

SELECT SUM(the_val) AS value
FROM
  (SELECT DISTINCT basic_data.id,
                   att2.the_val
   FROM province_create
   INNER JOIN basic_data ON province_create.province = basic_data.province
   INNER JOIN att2 ON att2.church_id = basic_data.id
   WHERE province_create.block = 0
     AND att2.month = 'Feb'
     AND att2.year = '2017'
     AND basic_data.parish = 1
     AND att2.report = 'ATTENDANCE'
     AND province_create.disable = 0 ) t1;

The EXPLAIN report:

[1] => Array ( [0] => 1 [id] => 1 [1] => PRIMARY [select_type] => PRIMARY [2] => [table] => [3] => ALL [type] => ALL [4] => [possible_keys] => [5] => [key] => [6] => [key_len] => [7] => [ref] => [8] => 38339 [rows] => 38339 [9] => [Extra] => )

[2] => Array
    (
        [0] => 2
        [id] => 2
        [1] => DERIVED
        [select_type] => DERIVED
        [2] => province_create
        [table] => province_create
        [3] => ALL
        [type] => ALL
        [4] => kk,province,kkk
        [possible_keys] => kk,province,kkk
        [5] => 
        [key] => 
        [6] => 
        [key_len] => 
        [7] => 
        [ref] => 
        [8] => 261
        [rows] => 261
        [9] => Using where; Using temporary
        [Extra] => Using where; Using temporary
    )

[3] => Array
    (
        [0] => 2
        [id] => 2
        [1] => DERIVED
        [select_type] => DERIVED
        [2] => basic_data
        [table] => basic_data
        [3] => ref
        [type] => ref
        [4] => PRIMARY,kk,kkk,k,parish
        [possible_keys] => PRIMARY,kk,kkk,k,parish
        [5] => kk
        [key] => kk
        [6] => 56
        [key_len] => 56
        [7] => databaseuser.province_create.province
        [ref] => databaseuser.province_create.province
        [8] => 39
        [rows] => 39
        [9] => Using index; Distinct
        [Extra] => Using index; Distinct
    )

[4] => Array
    (
        [0] => 2
        [id] => 2
        [1] => DERIVED
        [select_type] => DERIVED
        [2] => att2
        [table] => att2
        [3] => ref
        [type] => ref
        [4] => indpull,mmm
        [possible_keys] => indpull,mmm
        [5] => mmm
        [key] => mmm
        [6] => 57
        [key_len] => 57
        [7] => databaseuser.basic_data.id
        [ref] => databaseuser.basic_data.id
        [8] => 1
        [rows] => 1
        [9] => Using where; Distinct
        [Extra] => Using where; Distinct
    )

)

Upvotes: 0

Views: 427

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

First, let me assume that SELECT DISTINCT is not needed. Then the query can be written as:

SELECT SUM(a.the_val)
FROM province_create pc INNER JOIN
     basic_data bd
     ON pc.province = bd.province INNER JOIN
     att2 a
     ON a.church_id = bd.id
WHERE pc.block = 0 AND
      a.month = 'Feb' AND
      a.year = '2017' AND
      bd.parish = 1 AND
      a.report = 'ATTENDANCE'
      pc.disable = 0 ;

Second, you should try indexes on the tables. It is hard to tell what the best index would be, so try adding the following:

  • attr2(year, month, report, church_id, the_val)
  • basic_data(id, province, parish)
  • province_create(province, disable)

This index should help even if the SELECT DISTINCT is needed. However, you need to understand why you are getting duplicates and fix the root cause of that problem for best performance.

Upvotes: 1

Related Questions