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
   

   View Slide

2. GOOGLE SUMMER OF CODE
   STUDENT
   MENTOR
   

   View Slide

3. SILVERSTRIPE AUSTRIA
   VIENNADB
   PAPERS WE LOVE VIENNA
   

   View Slide

4. #1 REASON FOR
   SLOW QUERIES?
   

   View Slide

5. View Slide

6. View Slide

7. View Slide

8. FACTORS
   NOT PERCENT
   

   View Slide

9. How?
   

   View Slide

10. View Slide

11. View Slide

12. B+-TREES
    

    View Slide

13. HTTP://WWWOLD.CS.UMD.EDU/CLASS/FALL2002/CMSC818S/READINGS/B-TREE.PDF
    

    View Slide

14. MYSQL
    

    View Slide

15. HTTP://GITHUB.COM/JEREMYCOLE/INNODB_DIAGRAMS
    

    View Slide

16. No, how?
    

    View Slide

17. View Slide

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;
    

    View Slide

19. CREATE INDEX Version_RecordID
    ON SiteTree_versions (Version, RecordID);
    

    View Slide

20. View Slide

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;
    

    View Slide

22. RULE OF THUMB
    1. EXACT MATCHES
    2. RANGES
    3. ORDER
    

    View Slide

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;
    

    View Slide

24. CREATE INDEX Created
    ON SiteTree (Created);
    SELECT Title, Created
    FROM SiteTree
    WHERE YEAR(Created) = '2015';
    

    View Slide

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');
    

    View Slide

26. BEWARE OF DATABASE
    FUNCTIONS
    

    View Slide

27. CREATE INDEX Title ON SiteTree (Title);
    SELECT ID, Title
    FROM SiteTree
    WHERE Title LIKE '%foo%';
    

    View Slide

28. INNODB FULL-TEXT SEARCH
    (FTS)
    MYSQL 5.6+
    

    View Slide

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/
    

    View Slide

30. HTTP://USE-THE-INDEX-LUKE.COM
    

    View Slide

31. No really, how?
    

    View Slide

32. SILVERSTRIPE
    

    View Slide

33. class MyObject extends DataObject {
    private static $indexes = array(
    '' => true,
    '' => array('type' => '', 'value' => '""'),
    '' => 'unique("")'
    );
    }
    

    View Slide

34. class MyTestObject extends DataObject {
    private static $db = array(
    'MyField' => 'Varchar',
    'MyOtherField' => 'Varchar',
    );
    private static $indexes = array(
    'MyIndexName' => array(
    'type' => 'index',
    'value' => '"MyField","MyOtherField"'
    )
    );
    }
    

    View Slide

35. TYPE
    index
    unique
    fulltext
    RAW SQL
    

    View Slide

36. Permission.php
    private static $indexes = array(
    "Code" => true
    );
    

    View Slide

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
    //)
    );
    

    View Slide

38. FulltextFilter.php
    static $indexes = array(
    'SearchFields' => 'fulltext(Name, Title, Description)'
    );
    

    View Slide

39. Ok, but when?
    

    View Slide

40. SLOW QUERY LOG
    HTTPS://DEV.MYSQL.COM/DOC/REFMAN/5.6/EN/SLOW-QUERY-LOG.HTML
    

    View Slide

41. EXPLAIN
    

    View Slide

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";
    

    View Slide

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
    

    View Slide

44. type
    ALL = FULL TABLE SCAN
    !
    

    View Slide

45. type
    index = INDEX FULL SCAN
    !
    

    View Slide

46. type
    ref, range = INDEX RANGE SCAN
    !
    

    View Slide

47. type
    eq_ref, const = TREE TRAVERSAL
    !
    

    View Slide

48. Extra
    Using Index = INDEX ONLY SCAN
    !
    

    View Slide

49. CONCLUSION
    

    View Slide

50. View Slide

51. View Slide

52. THE QUESTION IS
    ❓ ➡ #
    ! ➡ ❓
    

    View Slide