My MySQL SQL Presentation

67 %
33 %
Information about My MySQL SQL Presentation
Technology

Published on March 4, 2014

Author: jerhinesmith

Source: slideshare.net

Description

Brief introduction to indexing within MySQL

Indexing with MySQL

Most of our data lives in MySQL We want to get it as efficiently as possible How?

Small tables?

Small tables? Not always practical

Small tables? Not always practical Indexes?

Small tables? Not always practical Indexes? OK, but how?

First things first:

First things first: What are indexes used for?

How do indexes work?

How do indexes work? Storage Types

BTree

BTree Ordered key-value map

BTree Ordered key-value map Most common storage type

BTree Ordered key-value map Most common storage type Quickly find a given key and can be scanned in order

BTree Ordered key-value map Most common storage type Quickly find a given key and can be scanned in order Works well with ranges All records between 50 and 100 All records starting with 'R'

Other storage types

Other storage types RTree / Spatial Index - Identify 'close' values in 2+ dimensions - Useful for geographic databases

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

How do indexes work? Pointers

Pointers

Pointers 1 Charles Mata Engineer 2 Aaron Macy Engineer 3 Kevin Clement QA 4 Jeremy Tan Manager

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

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

Index Types

Single column

Single column

Composite (multiple column)

Composite (multiple column)

Covering

Covering

Covering

Partial

Partial

Partial

Downsides?

Indexes take up SPACE

Indexes slow down INSERTS

Indexes confuse OPTIMIZATION

Selectivity

Selectivity of a column is the ratio between the number of distinct values and the number of total values

Selectivity of a column is the ratio between the number of distinct values and the number of total values Primary Keys and Unique Columns Always Have Selectivity of 1

Selectivity Tips

Selectivity Tips Always aim for >15%

Selectivity Tips Always aim for >15% Joins on columns with low selectivity are expensive

Selectivity Tips Always aim for >15% Joins on columns with low selectivity are expensive Watch out for columns like `status`, `gender`, and `active`

What makes a good index?

Small Use smallest data type possible Consider partial indexes on varchar/char to increase selectivity

Filter Columns Try to identify columns that will be used to filter data

Join Columns Identify columns that will often be joined on/to from other tables

Covering Indexes A query that can use a covering index will be substantially faster than one that has to read from the table

Group/Sort Identify columns that will be used for grouping and sorting

Redundant Indexes

MySQL cannot use an index if the columns do not form a leftmost prefix of the index

Finding bad indexes

Slow query log

Slow query log

Slow query log

Explain

Explain

Explain

Add a comment

Related presentations

Related pages

MySQL :: Presentations

Presentations. Topic: ... Scaling Web Databases with Auto-Partitioning and SQL/NoSQL Access; More (6) » MySQL Cluster : ... MySQL Enterprise Edition.
Read more

Presentation — Effective MySQL

This presentation from the MySQL/NoSQL/Cloud Conference in Latin America provides my experiences of the most common problems managing MySQL Operations in ...
Read more

MySQL

MySQL Fabric - Ein Leitfaden zur Verwaltung von MySQL Hochverfügbarkeit und Skalierung; MySQL Cluster 7.3: Neue Funktionen für internettaugliche Leistung ...
Read more

PowerPoint to display MySQL Data - PresentationPoint

Learn how to use DataPoint to display MySQL data of a table. Ideal for display game results or votes etc from a data driven website with MySQL and PHP.
Read more

MySQL - YouTube

This is the official MySQL channel from Oracle's MySQL team! Subscribe to this channel to learn more about MySQL product features and tips. Learn more ...
Read more

MySQL :: MySQL Community Downloads

MySQL Community Downloads. MySQL Community ... or SQL interface supporting development and administration for the MySQL Server and is a component of ...
Read more

MySQL :: MySQL Downloads

MySQL Enterprise Edition (commercial) MySQL Enterprise Edition includes the most comprehensive set of advanced features and management tools for MySQL.
Read more

MySQL :: MySQL Utilities

MySQL Utilities provide a collection of command-line utilities that are used for maintaining and administering MySQL databases, including: Admin Utilities ...
Read more

MySQL – Wikipedia

Der Name MySQL setzt sich zusammen aus dem Vornamen My, den die Tochter des MySQL AB ... Replikation der SQL-Kommandos. Mit dem MySQL Cluster ...
Read more