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. 5.
  2. 6.
  3. 7.
  4. 9.
  5. 10.
  6. 11.
  7. 12.
  8. 14.
  9. 16.
  10. 17.
  11. 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;
  12. 20.
  13. 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;
  14. 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;
  15. 27.
  16. 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>")' ); }
  17. 34.

    class MyTestObject extends DataObject { private static $db = array(

    'MyField' => 'Varchar', 'MyOtherField' => 'Varchar', ); private static $indexes = array( 'MyIndexName' => array( 'type' => 'index', 'value' => '"MyField","MyOtherField"' ) ); }
  18. 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 //) );
  19. 41.
  20. 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";
  21. 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
  22. 50.
  23. 51.