orm or sql? a better way to query in mysql
DESCRIPTION
The problem with frameworks and ORMs is the lackadaisical manner with which they write queries. Their focus is to straddle multiple database engines, not to write queries in the optimal way. The database layer is always the hardest layer to scale. Why would you put additional strain on your database to avoid writing SQL? This session focuses on understanding indices, queries, and optimization in MySQL.TRANSCRIPT
ORM OR SQL?A BETTER WAY TO QUERY IN MYSQL
Matthew Barlocker
Lucid Software Inc
ABOUT “THE BARLOCKER”
• Chief Architect at Lucid Software Inc
• Bachelors degree from BYU in Computer Science
• I love to
• play board games
• go 4-wheeling
• wrestle my sons
• fly airplanes
• Follow me on nineofclouds.blogspot.com
THE DATABASE LAYER
SCALING DATABASES IS HARD
Start with a single
server
Develop features
rapidly
Low operational cost
No redundancy
SCALING DATABASES IS HARD
Split database from
application layer
Add more application
servers
Start load balancing
traffic
SCALING DATABASES IS HARD
Add a hot backup of
the database
Add more application
servers
SCALING DATABASES IS HARD
Start doing reads on
the slave
Add a caching layer
Add more application
servers
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
PROTECTING YOUR DATABASE
WHEN IT COMES TO QUERIES…
FRAMEWORKS ARE YOUR ENEMIES
FRAMEWORKS HATE WHEN YOU SLEEP
FRAMEWORKS CREATE SIDE EFFECTS
Extra Joins
Wrong Kind of Joins
Default Ordering
Empty Array turned
into “IS NULL”
FRAMEWORKS HAVE OTHER GOALS
Make it easy to create a website for novices
Make it easy to access your data
Support all databases (by making an abstraction
layer for data persistence)
WHAT IS SQL?
SQL IS AN ABSTRACTION
FOR DATA PERSISTENCE
MYSQL OPTIMIZATIONS
EXPLAIN HELPS WITH OPTIMIZATION
The select_type will vary based on what your
query is. SIMPLE is the best type.
The optimizer will select from possible_keys the
best key to index on. It’s choice will be in key.
If possible_keys is NULL, a full table scan will be
performed.
The rows column will give the estimated number
of rows to check, using the selected index.
EXPLAIN SELECT * FROM mytable;
http://dev.mysql.com/doc/refman/5.6/en/select-optimization.html
SIMPLE QUERIES ARE… SIMPLE
INSERT INTO (…) VALUES (…);
UPDATE … WHERE indexedfield = 5;
DELETE … WHERE indexedfield = 5;
SELECT * FROM … WHERE indexedfield = 5;
COMPLEX INDICES FIX SLOWNESS
Complex indices can fix file sorts and temporary
tables (from the explain statement)
A complex index contains multiple fields, in a
particular order.
The left-most prefix index can be used on its own.
SELECT * FROM t1 ORDER BY keypart1,
keypart2;
SELECT * FROM t1 WHERE keypart1 <
constant ORDER BY keypart1 DESC;
SELECT kp1, kp2 FROM t1 GROUP BY kp1,
kp2;
COVERED INDICES ARE THE FASTEST
A covered index prevents a query from getting
rows from the disk.
Covered indices will have “Using Index” in the
explain output.
SELECT kp1, kp2 FROM t1 WHERE kp1 IS
NOT NULL;
SELECT MAX(kp1) FROM t1;
JOINS ARE BAD
Joins require temporary table space and join
buffer.
Temporary (joined) tables don’t have indices.
Joins don’t always work in a sharded
environment.
Joins are less likely to be in the query cache.
THANKS FOR YOUR TIME
Any Questions?
JO
INT
HE
TE
AM
• Building the
next
generation of
collaborative
web
applications
• VC funded
• High growth
rate
• Profitable
• Graduates
from Harvard,
MIT, Stanford
• Former
Google,
Amazon,
Microsoft
employees
https://www.golucid.co/jobs