Speeding Up Your Database

An interactive and informative talk on how you can implement effective Indexes with SilverStripe for optimal Database performance.

Transcript

1. SPEEDING UP YOUR DATABASE PHILIPP KRENN̴̴̴@xeraa
2. GOOGLE SUMMER OF CODE STUDENT MENTOR
3. SILVERSTRIPE AUSTRIA VIENNADB PAPERS WE LOVE VIENNA
4. #1 REASON FOR SLOW QUERIES?
8. FACTORS NOT PERCENT
9. How?
12. B+-TREES
13. HTTP://WWWOLD.CS.UMD.EDU/CLASS/FALL2002/CMSC818S/READINGS/B-TREE.PDF
14. MYSQL
15. HTTP://GITHUB.COM/JEREMYCOLE/INNODB_DIAGRAMS
16. No, how?
18. CREATE TABLE `SiteTree_versions` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RecordID` int(11) NOT NULL DEFAULT '0', `Version` int(11) NOT NULL DEFAULT '0', ... PRIMARY KEY (`ID`), KEY `RecordID_Version` (`RecordID`,`Version`), ... ) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; SELECT ID, RecordID, Version FROM SiteTree_versions WHERE RecordID = 123 AND Version = 4; SELECT ID, RecordID, Version FROM SiteTree_versions WHERE Version = 4;
19. CREATE INDEX Version_RecordID ON SiteTree_versions (Version, RecordID);
21. CREATE TABLE `SiteTree_versions` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `RecordID` int(11) NOT NULL DEFAULT '0', `Version` int(11) NOT NULL DEFAULT '0', ... PRIMARY KEY (`ID`), KEY `RecordID_Version` (`RecordID`,`Version`), ... ) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; SELECT ID, RecordID, Version FROM SiteTree_versions WHERE RecordID = 123 ORDER BY Version DESC LIMIT 1;
22. RULE OF THUMB 1. EXACT MATCHES 2. RANGES 3. ORDER
23. CREATE TABLE `SiteTree` ( ... `Title` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `Created` datetime DEFAULT NULL, ... ) ENGINE=InnoDB AUTO_INCREMENT=516 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
24. CREATE INDEX Created ON SiteTree (Created); SELECT Title, Created FROM SiteTree WHERE YEAR(Created) = '2015';
25. SELECT Title, Created FROM SiteTree WHERE Created >= STR_TO_DATE('2015-01-01', '%Y-%m-%d') AND Created < STR_TO_DATE('2016-01-01', '%Y-%m-%d');
26. BEWARE OF DATABASE FUNCTIONS
27. CREATE INDEX Title ON SiteTree (Title); SELECT ID, Title FROM SiteTree WHERE Title LIKE '%foo%';
28. INNODB FULL-TEXT SEARCH (FTS) MYSQL 5.6+
29. WATCH OUT FTS MYISAM != FTS INNODB HTTPS://WWW.PERCONA.COM/BLOG/2013/02/26/MYISAM-VS-INNODB-FULL-TEXT-SEARCH-IN-MYSQL-5-6-PART-1/
30. HTTP://USE-THE-INDEX-LUKE.COM
31. No really, how?
32. SILVERSTRIPE
33. class MyObject extends DataObject { private static $indexes = array( '<column-name>' => true, '<index-name>' => array('type' => '<type>', 'value' => '"<column-name>"'), '<index-name>' => 'unique("<column-name>")' ); }
34. class MyTestObject extends DataObject { private static $db = array( 'MyField' => 'Varchar', 'MyOtherField' => 'Varchar', ); private static $indexes = array( 'MyIndexName' => array( 'type' => 'index', 'value' => '"MyField","MyOtherField"' ) ); }
35. TYPE index unique fulltext RAW SQL
36. Permission.php private static $indexes = array( "Code" => true );
37. Member.php private static $indexes = array( 'Email' => true, //Removed due to duplicate null values causing MSSQL problems //'AutoLoginHash' => Array( // 'type'=>'unique', // 'value'=>'AutoLoginHash', // 'ignoreNulls'=>true //) );
38. FulltextFilter.php static $indexes = array( 'SearchFields' => 'fulltext(Name, Title, Description)' );
39. Ok, but when?
40. SLOW QUERY LOG HTTPS://DEV.MYSQL.COM/DOC/REFMAN/5.6/EN/SLOW-QUERY-LOG.HTML
41. EXPLAIN
42. CREATE TABLE `Feature` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Created` datetime DEFAULT NULL, `LastEdited` datetime DEFAULT NULL, `Category` enum('Shorts','Games','Open','Apps','Visuals','Photos') CHARACTER SET utf8 DEFAULT 'Shorts', ... ) ENGINE=InnoDB AUTO_INCREMENT=1110 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; SELECT ID FROM Feature WHERE Category = "Games";
43. EXPLAIN SELECT ID FROM Feature WHERE Category = "Games"; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE Feature ALL NULL NULL NULL NULL 787 Using where
44. type ALL = FULL TABLE SCAN !
45. type index = INDEX FULL SCAN !
46. type ref, range = INDEX RANGE SCAN !
47. type eq_ref, const = TREE TRAVERSAL !
48. Extra Using Index = INDEX ONLY SCAN !
49. CONCLUSION
52. THE QUESTION IS ❓ ➡ # ! ➡ ❓