Pro Yearly is on sale from $80 to $50! »

Speeding Up Your Database

Speeding Up Your Database

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

Ce4685da897c912aa41a815435b40a5a?s=128

Philipp Krenn

October 16, 2015
Tweet

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?

  5. None
  6. None
  7. None
  8. FACTORS NOT PERCENT

  9. How?

  10. None
  11. None
  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?

  17. None
  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);

  20. None
  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

  50. None
  51. None
  52. THE QUESTION IS ❓ ➡ # ! ➡ ❓