«
»

[Optimization] 注意 boolean 的選取方法去提升 SQL 的 efficiency


先看看這個sql，假設 book_published 和 best_seller 是boolean (bit)

1
2
3
4
5
6

SELECT DISTINCT a.author_id, a.author_name, b.book_id, b.book_title
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.author_id = 27
AND b.book_published = 1
AND b.best_seller = 1

如果dataset太大的話，run sql 的時候，他們就要內部convert　to　bit　再做比較。

只要把sql　簡單作修改，就可以避免不必要的type conversion 所花的時間。

1
2
3
4
5
6

SELECT DISTINCT a.author_id, a.author_name, b.book_id, b.book_title
FROM authors a
INNER JOIN books b ON a.author_id = b.author_id
WHERE a.author_id = 27
AND b.book_published = '1'　--只要加上quote，就不用type　conversion
AND b.best_seller = '1'　

Source: http://www.ravenglass.com/blog/index.cfm/2009/8/14/More-tips-for-SQL-efficiency-boolean-values-in-SQL

Share

This entry was posted on August 15, 2009, 3:49 pm and is filed under Database. You can follow any responses to this entry through RSS 2.0. You can leave a response, or trackback from your own site.

  1. No comments yet.
(will not be published)

Anti-Spam Protection by WP-SpamFree