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