$30 off During Our Annual Pro Sale. View Details »

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.

Philipp Krenn

October 16, 2015
Tweet

More Decks by Philipp Krenn

Other Decks in Programming

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 ❓ ➡ # ! ➡ ❓