Download - 10 performance tips we tend to forget
![Page 1: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/1.jpg)
10 PERFORMANCE TIPS WE TEND TO
FORGETEli Aschkenasy@EliAschkenasy
#JDNL16April 16, 2016
![Page 2: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/2.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
10 performance tips we tend to forget
3 Categories
1. Indexing2. Query Optimization3. Resource Handling
![Page 3: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/3.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Indexes have two purposes:
1. Speed up access to data2. (help) Enforce constraints
![Page 4: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/4.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization
Happens in conjunction with index strategy and only with real data.
![Page 5: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/5.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling
Might be the most overlooked yet easiest-to-fix part, but requires real data as well.
![Page 6: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/6.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Gather Data
![Page 7: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/7.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization – Logging Queries
mysql> SET GLOBAL slow_query_log = ON;
mysql> SET GLOBAL long_query_time = 0;
mysql> SET GLOBAL log_queries_not_using_indexes = ON;
![Page 8: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/8.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization – Logging Queries
Change to ON
![Page 9: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/9.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization – Analyzing Queries
Percona Toolkit for MySQL
![Page 10: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/10.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization – Analyzing Queries
http://nk.gl/slow_queries/analyze
For those of us who don’t want to commit.
![Page 11: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/11.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
WITH THIS DATAWE CAN CHECK THE IMPLEMENTATION
![Page 12: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/12.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization
![Page 13: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/13.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization
Here is my schema, what indexes do I need?
![Page 14: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/14.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
DB Optimization
Index choice depends on the queries you run, not the data you have!
relational schema design is based on
DATA
indexdesign is based on
QUERIES
![Page 15: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/15.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
![Page 16: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/16.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’
works
Simple/Compound Searches
![Page 17: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/17.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’
works
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’
works
Simple/Compound Searches
![Page 18: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/18.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’
works
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’
works
SELECT * FROM PhoneBookWHERE f_name = ‘Hans’
doesn‘t work
Simple/Compound Searches
![Page 19: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/19.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name LIKE ‘Ku%’
works
Range Lookup
![Page 20: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/20.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name LIKE ‘Ku%’
works
SELECT * FROM PhoneBookWHERE l_name = ‘Ku%’AND f_name = ‘Hans’
doesn‘t work
Range Lookup
![Page 21: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/21.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name LIKE ‘Ku%’
works
SELECT * FROM PhoneBookWHERE l_name = ‘Ku%’AND f_name = ‘Hans’
doesn‘t work
Range Lookup
Tip #1:Remember: Any Range Comparison breaks index benefit for subsequent columns
![Page 22: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/22.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY f_name
works
Sorting
![Page 23: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/23.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY f_name
works
Sorting
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY phone
doesn‘t work
![Page 24: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/24.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY f_name
works
Sorting
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’ORDER BY phone
doesn‘t work
Tip #2:Remember: Index can only benefit the sorting of the column immediately following last column used for search
![Page 25: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/25.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’
works
Index – Only Search
Because phone is included in index we can select it without overhead even though we didn’t include it in the search
![Page 26: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/26.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name = ‘Hans’
works
Index – Only Search
Tip #3:Putting columns in index even if they are not part of the query might speed things up – COVERING INDEX
Because phone is included in index we can select it without overhead even though we didn’t include it in the search
![Page 27: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/27.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
idx (l_name, f_name, phone)
SELECT * FROM PhoneBookWHERE l_name = ‘Kuijpers’OR f_name = ‘Hans’
doesn‘t work
Disjoint Selector
additional index (f_name, phone) would be required
![Page 28: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/28.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (policy, name, type)
Index Size
![Page 29: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/29.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (policy, name, type)
SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’
Index Size
Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures
![Page 30: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/30.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (type(1))
SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’
Index Size
Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures
![Page 31: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/31.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (type(1))
SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’
Index Size
Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures
Trick #1:Use only part of the column as length of the index, but remember that it will break the ‘covering’ property.
![Page 32: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/32.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (type(1))
SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’
Index Size
Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures
Trick #1a:Specificity Check
SELECTCOUNT(DISTINCT(type)) AS total,COUNT(DISTINCT(LEFT(type,10))) AS t10,COUNT(DISTINCT(LEFT(type,20))) AS t20FROM Insurance Company;
![Page 33: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/33.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerInsurance Company:PolicyNumberAgentType
idx (type(1))
SELECT * FROM InsuranceCompanyWHERE type = ‘Vervoerdersaansprakelijkheidsverzekering’
Index Estimation
Type Options: 1. Vervoerdersaansprakelijkheidsverzekering2. Bestuurdersaansprakelijkheidsverzekering3. Overeenstemmingsbeoordelingsprocedures
Trick #1b:Cardinality
ANALYZE TABLES;
![Page 34: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/34.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index PrimerPhone Book:Last Name (ASC)First Name (ASC)Phone Number (RAND)
MySQL 5.5
idx (l_name) – traditionalidx (l_name, f_name, phone) – covering
Range Lookup
SELECT phone FROM PhoneBookWHERE l_name = ‘Kuijpers’AND f_name LIKE ‘%Hans%’
MySQL 5.6
idx (l_name, f_name)Range access l_name, Filter clause on f_name (only read if full row match)
![Page 35: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/35.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index StrategyChoose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2
![Page 36: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/36.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index StrategyChoose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (b, a) is better than KEY (a, b)
![Page 37: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/37.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index Strategy (Trick #2)
Choose Index order which benefits more queries• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (b, a) is better than KEY (a, b)
Index order (without restrictions) should be most selective to least selective
![Page 38: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/38.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index Strategy (Trick #1)
• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (a, b)
• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2
![Page 39: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/39.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index Strategy (Trick #1)
• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (a, b)
• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2KEY (a, b)
![Page 40: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/40.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #1)
• SELECT * FROM t WHERE a=1 AND b=2• SELECT * FROM t WHERE a>1 AND b=2KEY (a, b)
• SELECT * FROM t WHERE a BETWEEN 2 AND 4 AND b=2KEY (a, b)
• SELECT * FROM t WHERE a IN(2,3,4) AND b=2KEY (a, b)
Enumerating Ranges will ensure the usage of both key parts
![Page 41: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/41.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #2)
CREATE TABLE profile( id INT, gender CHAR(1), age TINYINT(3), city VARCHAR(100), PRIMARY KEY(id));
SELECT id FROM profileWHERE city = “Amsterdam”AND age BETWEEN 35 AND 40
KEY (city, age, id)
![Page 42: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/42.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #2)
CREATE TABLE profile( id INT, gender CHAR(1), age TINYINT(3), city VARCHAR(100), PRIMARY KEY(id));
SELECT id FROM profileWHERE city = “Amsterdam”AND age BETWEEN 35 AND 40
KEY (city, age, id)
SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age BETWEEN 35 AND 40
KEY (city, gender, age, id)
![Page 43: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/43.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Index Strategy (Trick #3)
CREATE TABLE profile( id INT, gender CHAR(1), age TINYINT(3), city VARCHAR(100), PRIMARY KEY(id));
SELECT id FROM profileWHERE city = “Amsterdam”AND age BETWEEN 35 AND 40
KEY (city, gender, age, id)
SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age BETWEEN 35 AND 40
KEY (city, gender, age, id)Trick #3:Use as little indexes as possible. A little query rewrite could save massive overhead on the index-side.
![Page 44: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/44.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #2)
SELECT id FROM profileWHERE city = “Amsterdam”AND age BETWEEN 35 AND 40
KEY (city, gender, age, id)
SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age BETWEEN 35 AND 40
KEY (city, gender, age, id)
SELECT id FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)
KEY (city, gender, age, id)
SELECT id FROM profileWHERE city = “Amsterdam”AND gender = “F”AND age IN(35,36,37,38,39,40)
KEY (city, gender, age, id)
![Page 45: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/45.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #1)
SELECT id, name, picture, … , age FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10
(page 11)
Retrieves 110 rows and discards 100
![Page 46: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/46.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #1)
SELECT id, name, picture, … , age FROM profileWHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10
(page 11)
Retrieves 110 rows and discards 100
SELECT id, name, picture, … , ageFROM profile INNER JOIN( SELECT id FROM profile
WHERE city = “Amsterdam”AND gender IN(‘M’,’F’,’O’)AND age IN(35,36,37,38,39,40)ORDER BY ratingLIMIT 100, 10)
AS xUSING (id);
(page 11)
Retrieves 110 ids and discards 100Retrieves only 10 rows of data
![Page 47: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/47.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #3)
SELECT id FROM urlWHERE url="http://www.joomladagen.nl";
CREATE TABLE pseudohash ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, url_crc INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(id));
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
SELECT id FROM url WHERE url_crc=CRC32("http://www.joomladagen.nl") AND url="http://www.joomladagen.nl";
![Page 48: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/48.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #3)
SELECT id FROM urlWHERE url="http://www.joomladagen.nl";
CREATE TABLE pseudohash ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL, url_crc INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(id));
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN SET NEW.url_crc=crc32(NEW.url);
SELECT id FROM url WHERE url_crc=CRC32("http://www.joomladagen.nl") AND url="http://www.joomladagen.nl";
Fixed length index Fixed length data
![Page 49: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/49.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2)
SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Hans Kuijpers’;
![Page 50: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/50.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2)
SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
![Page 51: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/51.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2)
SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
CREATE TABLE comment ( id INT(11) NOT NULL…, tag VARCHAR(255)…, importance TINYINT(3)…, image BLOB…, comment TEXT…, created DATETIME…, created_by INT(11)…, modified…, …., …., publish_up DATETIME, PRIMARY KEY(`id`)) ENGINE=InnoDB;
![Page 52: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/52.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2)
SELECT * FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
CREATE TABLE comment ( id INT(11) NOT NULL…, tag VARCHAR(255)…, importance TINYINT(3)…, image BLOB…, comment TEXT…, created DATETIME…, created_by INT(11)…, modified…, …., …., publish_up DATETIME, PRIMARY KEY(`id`)) ENGINE=InnoDB;
![Page 53: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/53.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2)
SELECT c.comment FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
CREATE TABLE comment ( id INT(11) NOT NULL…, tag VARCHAR(255)…, importance TINYINT(3)…, image BLOB…, comment TEXT…, created DATETIME…, created_by INT(11)…, modified…, …., …., publish_up DATETIME, PRIMARY KEY(`id`)) ENGINE=InnoDB;
![Page 54: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/54.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Resource Handling (Trick #2a)
SELECT SUBSTRING(c.comment, 0, 25) AS short_comment FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
CREATE TABLE comment ( id INT(11) NOT NULL…, tag VARCHAR(255)…, importance TINYINT(3)…, image BLOB…, comment TEXT…, created DATETIME…, created_by INT(11)…, modified…, …., …., publish_up DATETIME, PRIMARY KEY(`id`)) ENGINE=InnoDB;
![Page 55: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/55.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
Query Optimization (Trick #4)
SELECT SUBSTRING(c.comment, 0, 25) AS short_comment FROM comment AS c LEFT JOIN article AS a ON a.id_comment = c.id LEFT JOIN users AS u ON a.created_by = u.idWHERE a.title=‘joomladagen‘AND u.name = ‘Jisse Reitsma’;
SELECT id FROM usersWHERE name = ‘Jisse Reitsma’; 57125
SELECT id FROM articleWHERE title = ‘Joomladagen’AND created_by = 57125; (123,223,934,1145)
SELECT SUBSTRING(comment, 0, 25) AS short_comment FROM commentWHERE id IN(123,223,934,1145);
Trick #4:Query cache validation of 3 tables.Complex queries reduce likelihood of repetition.
![Page 56: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/56.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
THANK YOU
![Page 57: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/57.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile TEXT…, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO profile (id, profile)VALUES (1, ‘{“handle”: “@EliAschkenasy”}’);
![Page 58: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/58.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile TEXT…, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO profile (id, profile)VALUES (1, ‘{“handle”: “@EliAschkenasy”}’);
SELECT id FROM profileWHERE field_name REGEXP '"handle":"([^"]*)@EliAschkenasy([^"]*)"';
![Page 59: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/59.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile TEXT…, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO profile (id, profile)VALUES (1, ‘{“handle”: “@EliAschkenasy”}’);
SELECT id FROM profileWHERE field_name REGEXP '"handle":"([^"]*)@EliAschkenasy([^"]*)"';
Full Table Scan (not cacheable just like full text search)
REGEX
![Page 60: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/60.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile JSON, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO profile (id, profile)VALUES (1, ‘{“id”: 1, “handle”: “@EliAschkenasy”}’);
SELECT JSON_EXTRACT(profile, ‘$.handle’) FROM profileWHERE id = 1; - @EliAschkenasy
![Page 61: 10 performance tips we tend to forget](https://reader036.vdocuments.us/reader036/viewer/2022062503/588b15801a28ab78398b457b/html5/thumbnails/61.jpg)
Eli Aschkenasy - @EliAschkenasy - #JDNL16
MySQL 5.7 – JSON CREATE TABLE profile( id INT(11)…, profile JSON, PRIMARY KEY (`id`)) ENGINE = InnoDB;
INSERT INTO profile (id, profile)VALUES (1, ‘{“id”: 1, “handle”: “@EliAschkenasy”}’);
SELECT JSON_EXTRACT(profile, ‘$.handle’) FROM profileWHERE id = 1; - @EliAschkenasy
JSON data type consumes about 5% more memory JSON columns can’t have a default value JSON columns can’t be indexed BETWEEN, IN(), GREATEST(), LEAST() Are NOT supported yet!