Download - My MySQL SQL Presentation
![Page 1: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/1.jpg)
Indexingwith MySQL
![Page 2: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/2.jpg)
Most of our data lives in MySQL
We want to get it as efficiently as possible
How?
![Page 3: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/3.jpg)
Small tables?
![Page 4: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/4.jpg)
Small tables? Not always practical
![Page 5: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/5.jpg)
Small tables? Not always practical
Indexes?
![Page 6: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/6.jpg)
Small tables? Not always practical
Indexes? OK, but how?
![Page 7: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/7.jpg)
First things first:
![Page 8: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/8.jpg)
First things first:What are indexes used for?
![Page 9: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/9.jpg)
![Page 10: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/10.jpg)
![Page 11: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/11.jpg)
![Page 12: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/12.jpg)
![Page 13: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/13.jpg)
![Page 14: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/14.jpg)
![Page 15: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/15.jpg)
![Page 16: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/16.jpg)
![Page 17: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/17.jpg)
How do indexes work?
![Page 18: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/18.jpg)
How do indexes work?Storage Types
![Page 19: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/19.jpg)
BTree
![Page 20: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/20.jpg)
BTree
Ordered key-value map
![Page 21: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/21.jpg)
BTree
Ordered key-value mapMost common storage type
![Page 22: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/22.jpg)
BTree
Ordered key-value mapMost common storage typeQuickly find a given key and can be scanned in order
![Page 23: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/23.jpg)
BTree
Ordered key-value mapMost common storage typeQuickly find a given key and can be scanned in orderWorks well with ranges
All records between 50 and 100All records starting with 'R'
![Page 24: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/24.jpg)
Other storage types
![Page 25: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/25.jpg)
Other storage types
RTree / Spatial Index - Identify 'close' values in 2+ dimensions - Useful for geographic databases
![Page 26: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/26.jpg)
Other storage types
RTree / Spatial Index - Identify 'close' values in 2+ dimensions - Useful for geographic databases
Hash - Unordered key/value map - Faster than BTree, but terrible for ranges
![Page 27: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/27.jpg)
How do indexes work?Pointers
![Page 28: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/28.jpg)
Pointers
![Page 29: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/29.jpg)
Pointers
1 Charles Mata Engineer 2
Aaron Macy Engineer 3 Kevin
Clement QA 4 Jeremy Tan
Manager
![Page 30: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/30.jpg)
Pointers
1 Charles Mata Engineer 2
Aaron Macy Engineer 3 Kevin
Clement QA 4 Jeremy Tan
Manager
Aaron 2
Charles 1
Jeremy 4
Kevin 3
![Page 31: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/31.jpg)
Pointers
1 Charles Mata Engineer 2
Aaron Macy Engineer 3 Kevin
Clement QA 4 Jeremy Tan
Manager
Aaron 2
Charles 1
Jeremy 4
Kevin 3
![Page 32: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/32.jpg)
Index Types
![Page 33: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/33.jpg)
Single column
![Page 34: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/34.jpg)
Single column
![Page 35: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/35.jpg)
Composite (multiple column)
![Page 36: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/36.jpg)
Composite (multiple column)
![Page 37: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/37.jpg)
Covering
![Page 38: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/38.jpg)
Covering
![Page 39: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/39.jpg)
Covering
![Page 40: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/40.jpg)
Partial
![Page 41: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/41.jpg)
Partial
![Page 42: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/42.jpg)
Partial
![Page 43: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/43.jpg)
Downsides?
![Page 44: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/44.jpg)
Indexes take up
SPACE
![Page 45: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/45.jpg)
Indexes slow down
INSERTS
![Page 46: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/46.jpg)
Indexes confuse
OPTIMIZATION
![Page 47: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/47.jpg)
Selectivity
![Page 48: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/48.jpg)
Selectivity of a column isthe ratio between the number of
distinct values and thenumber of total values
![Page 49: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/49.jpg)
Selectivity of a column isthe ratio between the number of
distinct values and thenumber of total values
Primary Keys and Unique ColumnsAlways Have Selectivity of 1
![Page 50: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/50.jpg)
Selectivity Tips
![Page 51: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/51.jpg)
Always aim for >15%
Selectivity Tips
![Page 52: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/52.jpg)
Always aim for >15%
Joins on columns with low selectivity are expensive
Selectivity Tips
![Page 53: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/53.jpg)
Always aim for >15%
Joins on columns with low selectivity are expensive
Watch out for columns like `status`, `gender`, and `active`
Selectivity Tips
![Page 54: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/54.jpg)
What makes a good index?
![Page 55: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/55.jpg)
Use smallest data type possible
Consider partial indexes on varchar/char to increase selectivity
Small
![Page 56: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/56.jpg)
Try to identify columns that will be used to filter data
Filter Columns
![Page 57: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/57.jpg)
Identify columns that will often be joined on/to from other tables
Join Columns
![Page 58: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/58.jpg)
Covering Indexes
A query that can use a covering index will be substantially faster than one that has to read from the table
![Page 59: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/59.jpg)
Group/Sort
Identify columns that will be used for grouping and sorting
![Page 60: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/60.jpg)
Redundant Indexes
![Page 61: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/61.jpg)
MySQL cannot use an indexif the columns do not form
a leftmost prefix of the index
![Page 62: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/62.jpg)
![Page 63: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/63.jpg)
![Page 64: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/64.jpg)
![Page 65: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/65.jpg)
![Page 66: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/66.jpg)
![Page 67: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/67.jpg)
![Page 68: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/68.jpg)
![Page 69: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/69.jpg)
![Page 70: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/70.jpg)
![Page 71: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/71.jpg)
Finding bad indexes
![Page 72: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/72.jpg)
Slow query log
![Page 73: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/73.jpg)
Slow query log
![Page 74: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/74.jpg)
Slow query log
![Page 75: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/75.jpg)
Explain
![Page 76: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/76.jpg)
Explain
![Page 77: My MySQL SQL Presentation](https://reader035.vdocuments.us/reader035/viewer/2022062514/557bc68bd8b42aac088b5648/html5/thumbnails/77.jpg)
Explain