CODEWITHSUNDEEP
mysqlsql
Beetlegeuse
Beetlegeuse

Reputation: 297

Specific Query optimization

I don't know much about how to optimize database querys for better speed and performance, I have this SQL and by what I see this query is performance consuming:

SELECT *, c.coefficient FROM tickets t INNER JOIN draws d 
  ON(FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
AND (FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)>0)
INNER JOIN coefficients c ON c.number = GREATEST(
FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35), 
FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35)
) 
WHERE...

Can anyone give me tips to optimize this query

Upvotes: 0

Views: 47

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

try this and see if it's any faster. trying to reduce the workload of FIELD() function calls

SELECT td.*,c.coefficient
FROM
    (SELECT *,
    FIELD(t.b1,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match1,
    FIELD(t.b2,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match2,
    FIELD(t.b3,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match3,
    FIELD(t.b4,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match4,
    FIELD(t.b5,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match5,
    FIELD(t.b6,d.1,d.2,d.3,d.4,d.5,d.6,d.7,d.8,d.9,d.10,d.11,d.12,d.13,d.14,d.15,d.16,d.17,d.18,d.19,d.20,d.21,d.22,d.23,d.24,d.25,d.26,d.27,d.28,d.29,d.30,d.31,d.32,d.33,d.34,d.35) as match6
    FROM tickets t
    CROSS JOIN draws d
    HAVING match1>0 AND match2>0 AND match3>0 AND match4>0 AND match5>0 AND match6>0
    )td
INNER JOIN coefficients c ON c.number =
    GREATEST(match1,match2,match3,match4,match5,match6)
WHERE ...

notice tickets t and draws d are now joined to create td so in your WHERE you might have to reference td instead of t and d.

Upvotes: 1

Related Questions