Download - ORM or SQL? A Better Way to Query in MySQL
![Page 1: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/1.jpg)
ORM OR SQL?A BETTER WAY TO QUERY IN MYSQL
Matthew Barlocker
Lucid Software Inc
![Page 2: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/2.jpg)
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
![Page 3: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/3.jpg)
THE DATABASE LAYER
![Page 4: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/4.jpg)
SCALING DATABASES IS HARD
Start with a single
server
Develop features
rapidly
Low operational cost
No redundancy
![Page 5: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/5.jpg)
SCALING DATABASES IS HARD
Split database from
application layer
Add more application
servers
Start load balancing
traffic
![Page 6: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/6.jpg)
SCALING DATABASES IS HARD
Add a hot backup of
the database
Add more application
servers
![Page 7: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/7.jpg)
SCALING DATABASES IS HARD
Start doing reads on
the slave
Add a caching layer
Add more application
servers
![Page 8: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/8.jpg)
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
![Page 9: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/9.jpg)
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
![Page 10: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/10.jpg)
SCALING DATABASES IS HARD
Scale Vertically Scale Horizontally
![Page 11: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/11.jpg)
PROTECTING YOUR DATABASE
![Page 12: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/12.jpg)
WHEN IT COMES TO QUERIES…
FRAMEWORKS ARE YOUR ENEMIES
![Page 13: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/13.jpg)
FRAMEWORKS HATE WHEN YOU SLEEP
![Page 14: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/14.jpg)
FRAMEWORKS CREATE SIDE EFFECTS
Extra Joins
Wrong Kind of Joins
Default Ordering
Empty Array turned
into “IS NULL”
![Page 15: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/15.jpg)
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)
![Page 16: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/16.jpg)
WHAT IS SQL?
![Page 17: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/17.jpg)
SQL IS AN ABSTRACTION
FOR DATA PERSISTENCE
![Page 18: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/18.jpg)
MYSQL OPTIMIZATIONS
![Page 19: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/19.jpg)
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
![Page 20: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/20.jpg)
SIMPLE QUERIES ARE… SIMPLE
INSERT INTO (…) VALUES (…);
UPDATE … WHERE indexedfield = 5;
DELETE … WHERE indexedfield = 5;
SELECT * FROM … WHERE indexedfield = 5;
![Page 21: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/21.jpg)
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;
![Page 22: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/22.jpg)
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;
![Page 23: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/23.jpg)
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.
![Page 24: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/24.jpg)
THANKS FOR YOUR TIME
Any Questions?
![Page 25: ORM or SQL? A Better Way to Query in MySQL](https://reader034.vdocuments.us/reader034/viewer/2022052410/554f934eb4c905435d8b51c0/html5/thumbnails/25.jpg)
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