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.
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 —
USE INDEX, and one other one.
SHOW STATUS gives you status variables.
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
- only allowed in procedures, not functions
- depth controlled by
- iterative alternatives are almost always faster and scaleable
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/