masonwan
11/18/2013 - 4:48 AM

DbBenchmark.md

DbBenchmark.md

Screenshot

Output

Search all books which the title starts with "Survival".
========================================
Use normal query
28.2 ms
Use stored procedure
227.9 ms
Use view
25.0 ms
========================================
Create index
10.44 s
========================================
Use normal query
3.3 ms
Use stored procedure
4.3 ms
Use view
3.3 ms
========================================
Drop index
7.610 s

Query

  • Normal query
SELECT * FROM bookshare.`bx-books` where `Book-Title` like "Survival%";
  • Create stored procedure
DELIMITER $$
CREATE PROCEDURE `search_by_title`(IN title VARCHAR(128))
BEGIN
    SELECT * FROM bookshare.`bx-books` where `Book-Title` like title;
END$$
DELIMITER ;
  • Call store procedure
CALL search_by_title("Survival%");
  • Create view
CREATE VIEW `search_title` AS SELECT * FROM bookshare.`bx-books` where `Book-Title` like "Survival%";
  • Create index
ALTER TABLE `bookshare`.`bx-books` ADD INDEX `title` (`Book-Title` ASC);