Tuning MySQL5 SQL and Stored Procedures: Guy Harrison

This post dedicated to Edwin DeSouza.

Un-tuned SQL or stored procedures often fail to scale as table volumes increase, inefficiency increases exponentially with size.

Tune SQL/stored procedures and then buy new hardware.

use EXPLAIN to help optimize queries. Also use the slow query log.

EXPLAIN EXTENDED shows sql that was actually used — ie, optimizer may rewrite query, so it’s a neat tool.

you can always give optimizer hints, but they’re not recommended — keep checking them as your app grows — STRAIGHT_JOIN, FORCE INDEX, USE INDEX, and one other one.

SHOW STATUS gives you status variables. innodb_buffer_pool_read_requests and innodb_data_read will show how much data is being read from the buffer pool vs. data.

Index isn’t always used, if more than 20% or so of rows, MySQL will use a full table scan. There’s usually a range where MySQL will choose a full table scan when an index is more appropriate, or vice versa, so that’s when you’d use hints. Hey, nobody’s perfect!

think indexes — joining tables of non-trivial size Subqueries ( [NOT] EXISTS, [NOT] IN) in WHERE clause. Use index to avoid a sort, use “covering” indexes.

Establish the best set of multi-column indexes along with singular indexes.

Derived tables (subqueries in FROM cause) can’t use an index. VIEWs with UNION or GROUP BY also can’t use index — all these use TEMPTABLE view algorithm. (temp table created, and then reads from temp table).

Sorts can be improved by increasing memory (sort_buffer_size) or using an index.

Use procedures to:

  • Avoid self joins
  • Correlated updates (subqueries accessing same data)

Performance of SQL within a stored routine that dominates the performance. When SQL is tuned, optimize the routine using traditional techniques:

  • only put what’s needed in a loop
  • stop testing when you know the answer
  • order tests by most likely first

Recursion:

  • only allowed in procedures, not functions
  • depth controlled by max_sp_recursion_depth
  • iterative alternatives are almost always faster and scaleable

TRIGGERS
non-trivial (12% at least) to even simplest trigger. No trigger should EVER contain expensive SQL, because they are done for each row.

Quest free software for MySQL — http://www.quest.com/mysql/

This post dedicated to Edwin DeSouza.

Un-tuned SQL or stored procedures often fail to scale as table volumes increase, inefficiency increases exponentially with size.

Tune SQL/stored procedures and then buy new hardware.

use EXPLAIN to help optimize queries. Also use the slow query log.

EXPLAIN EXTENDED shows sql that was actually used — ie, optimizer may rewrite query, so it’s a neat tool.

you can always give optimizer hints, but they’re not recommended — keep checking them as your app grows — STRAIGHT_JOIN, FORCE INDEX, USE INDEX, and one other one.

SHOW STATUS gives you status variables. innodb_buffer_pool_read_requests and innodb_data_read will show how much data is being read from the buffer pool vs. data.

Index isn’t always used, if more than 20% or so of rows, MySQL will use a full table scan. There’s usually a range where MySQL will choose a full table scan when an index is more appropriate, or vice versa, so that’s when you’d use hints. Hey, nobody’s perfect!

think indexes — joining tables of non-trivial size Subqueries ( [NOT] EXISTS, [NOT] IN) in WHERE clause. Use index to avoid a sort, use “covering” indexes.

Establish the best set of multi-column indexes along with singular indexes.

Derived tables (subqueries in FROM cause) can’t use an index. VIEWs with UNION or GROUP BY also can’t use index — all these use TEMPTABLE view algorithm. (temp table created, and then reads from temp table).

Sorts can be improved by increasing memory (sort_buffer_size) or using an index.

Use procedures to:

  • Avoid self joins
  • Correlated updates (subqueries accessing same data)

Performance of SQL within a stored routine that dominates the performance. When SQL is tuned, optimize the routine using traditional techniques:

  • only put what’s needed in a loop
  • stop testing when you know the answer
  • order tests by most likely first

Recursion:

  • only allowed in procedures, not functions
  • depth controlled by max_sp_recursion_depth
  • iterative alternatives are almost always faster and scaleable

TRIGGERS
non-trivial (12% at least) to even simplest trigger. No trigger should EVER contain expensive SQL, because they are done for each row.

Quest free software for MySQL — http://www.quest.com/mysql/