Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
Newer
Older
100644 122 lines (100 sloc) 6.144 kB
dd8dba2 @ronaldbradford Remaining chapters
ronaldbradford authored
1 # Effective MySQL: Optimizing SQL Statements by Ronald Bradford
2 # http://effectivemysql.com/book/optimizing-sql-statements
3 #
4
5 #
6 # chapter04.sql
7 #
8 CREATE SCHEMA IF NOT EXISTS book;
9 USE book;
10
11 #Existing Indexes
12 SELECT artist_id, type, founded FROM artist WHERE name = 'Coldplay';
13 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE name = 'Coldplay'\G
14 SHOW CREATE TABLE artist\G
15
16 # Restricting rows with an index
17 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE founded=1942\G
18 ALTER TABLE artist ADD INDEX (founded);
19 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE founded=1942\G
20 ALTER TABLE artist ADD INDEX (founded);
21 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE founded=1942\G
22
23 # Joing tables with an index
24 EXPLAIN SELECT ar.name, ar.founded, al.name, al.first_released FROM artist ar INNER JOIN album al USING (artist_id) WHERE ar.name = 'Queen'\G
25 ALTER TABLE album ADD INDEX (artist_id);
26 EXPLAIN SELECT ar.name, ar.founded, al.name, al.first_released FROM artist ar INNER JOIN album al USING (artist_id) WHERE ar.name = 'Queen'\G
27
28 # Understanding index cardinality
29 ALTER TABLE artist ADD INDEX (type);
30 SET @@session.optimizer_switch='index_merge_intersection=off';
31 EXPLAIN SELECT artist_id, name, country_id FROM artist WHERE founded = 1980 AND type='Band'\G
32 SHOW INDEXES FROM artist\G
33 EXPLAIN SELECT artist_id, name, country_id FROM artist WHERE founded BETWEEN 1980 AND 1989 AND type='Band'\G
34 EXPLAIN SELECT artist_id, name, country_id FROM artist WHERE founded BETWEEN 1980 AND 1989 AND type='Combination'\G
35
36 #Using indexes for pattern matching
37 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE name LIKE 'Queen%'\G
38 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE name LIKE '%Queen%'\G
39 EXPLAIN SELECT artist_id, type, founded FROM artist WHERE UPPER(name) = UPPER('Billy Joel')\G
40
41 # Selecting a unique row
42 FLUSH STATUS;
43 SHOW SESSION STATUS LIKE 'Handler_read_next';
44 SELECT name FROM artist WHERE name ='Enya';
45 SHOW SESSION STATUS LIKE 'Handler_read_next';
46
47 ALTER TABLE artist DROP INDEX name,ADD UNIQUE INDEX(name);
48
49 FLUSH STATUS;
50 SHOW SESSION STATUS LIKE 'Handler_read_next';
51 SELECT name FROM artist WHERE name ='Enya';
52 SHOW SESSION STATUS LIKE 'Handler_read_next';
53
54
55 # Ordering Results
56 EXPLAIN SELECT name,founded FROM artist WHERE name like 'AUSTRALIA%' ORDER BY founded\G
57 FLUSH STATUS;
58 SELECT name,founded FROM artist WHERE name like 'AUSTRALIA%' ORDER BY founded\G
59 SHOW SESSION STATUS LIKE '%sort%';
60 EXPLAIN SELECT name,founded FROM artist WHERE name like 'AUSTRALIA%' ORDER BY name\G
61 FLUSH STATUS;
62 SELECT name,founded FROM artist WHERE name like 'AUSTRALIA%' ORDER BY name\G
63 SHOW SESSION STATUS LIKE '%sort%';
64
65 # Determining which index to use
66 ALTER TABLE album ADD INDEX (country_id), ADD INDEX (album_type_id);
67 SET @@session.optimizer_switch='index_merge_intersection=off';
68 EXPLAIN SELECT al.name, al.first_released, al.album_type_id FROM album al WHERE al.country_id=221 AND album_type_id=1\G
69 EXPLAIN SELECT al.name, al.first_released, al.album_type_id FROM album al WHERE al.country_id=221 AND album_type_id=4\G
70 SHOW INDEXES FROM album\G
71 SELECT COUNT(*) FROM album where country_id=221;
72 SELECT COUNT(*) FROM album where album_type_id=4;
73 SELECT COUNT(*) FROM album where album_type_id=1;
74
75 # Providing a Better Index
76 ALTER TABLE album ADD INDEX m1 (country_id, album_type_id);
77 EXPLAIN SELECT al.name, al.first_released, al.album_type_id FROM album al WHERE al.country_id=221 AND album_type_id=4\G
78 ALTER TABLE album ADD INDEX m2 (album_type_id,country_id);
79 EXPLAIN SELECT al.name, al.first_released, al.album_type_id FROM album al WHERE al.country_id=221 AND album_type_id=4\G
80 SHOW INDEXES FROM album\G
81
82 # Many column indexes
83 ALTER TABLE artist ADD index (type,gender,country_id);
84 EXPLAIN SELECT name FROM artist WHERE type= 'Person' AND gender='Male' AND country_id = 13\G
85 EXPLAIN SELECT name FROM artist WHERE type= 'Person' AND gender='Male'\G
86
87 # Combining WHERE and ORDER BY
88 ALTER TABLE album ADD INDEX (name);
89 EXPLAIN SELECT a.name, ar.name, a.first_released FROM album a INNER JOIN artist ar USING (artist_id) WHERE a.name = 'Greatest Hits' ORDER BY a.first_released\G
90 ALTER TABLE album ADD INDEX name_release (name,first_released);
91 EXPLAIN SELECT a.name, ar.name, a.first_released FROM album a INNER JOIN artist ar USING (artist_id) WHERE a.name = 'Greatest Hits' ORDER BY a.first_released\G
92
93 # MySQL optimizer features
94 SET @@session.optimizer_switch='index_merge_intersection=on';
95 EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR founded = 1942\G
96 EXPLAIN SELECT artist_id, name FROM artist WHERE type = 'Band' AND founded = 1942\G
97 EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR (founded BETWEEN 1942 AND 1950)\G
98 EXPLAIN SELECT artist_id, name FROM artist WHERE name = 'Queen' OR (type = 'Band' AND founded = 1942)\G
99
100 # Query Hints
101 EXPLAIN SELECT album.name, artist.name, album.first_released FROM artist INNER JOIN album USING (artist_id) WHERE album.name = 'Greatest Hits'\G
102 EXPLAIN SELECT STRAIGHT_JOIN album.name, artist.name, album.first_released FROM artist INNER JOIN album USING (artist_id) WHERE album.name = 'Greatest Hits'\G
103 EXPLAIN SELECT artist_id, name, country_id FROM artist WHERE founded = 1980 AND type='Band'\G
104 EXPLAIN SELECT artist_id, name, country_id FROM artist USE INDEX (type) WHERE founded = 1980 AND type='Band'\G
105 EXPLAIN SELECT artist_id, name, country_id FROM artist IGNORE INDEX (founded) WHERE founded = 1980 AND type='Band'\G
106 EXPLAIN SELECT artist_id, name, country_id FROM artist IGNORE INDEX (founded,founded_2) USE INDEX (type_2) WHERE founded = 1980 AND type='Band'\G
107
108 # DML Impact
109 DROP TABLE IF EXISTS t1;
110 CREATE TABLE t1 LIKE album;
111 INSERT INTO t1 SELECT * FROM album;
112 DROP TABLE t1;
113 CREATE TABLE t1 LIKE album;
114 #ALTER TABLE t1 DROP INDEX first_released, DROP INDEX album_type_id, DROP INDEX name, DROP INDEX country_id, DROP INDEX m1, DROP INDEX m2;
115 ALTER TABLE t1 DROP INDEX album_type_id, DROP INDEX country_id, DROP INDEX m1, DROP INDEX m2;
116 INSERT INTO t1 SELECT * FROM album;
117 DROP TABLE t1;
118
119
120
121 # END
Something went wrong with that request. Please try again.