Structured Query Language/Performance
Result set column
It is not recommended to use
* in a SELECT clause, due to performance issues. You should only return columns you want to use. As a consequence, you should replace any
count(*) by a count on one column only.
Avoid the value expressions
Whenever it is possible, avoid the use of value expressions in the WHERE clause like this:
WHERE duration - 60 <= 0;
It forces the rDBMS to compute the value for each line, which is very expensive. You should rather compute yourself the values with literals (
0 + 60 in this case):
WHERE duration <= 60;
If you often select records sorting or filtering by a given column, you may add an index on this column. The database behavior should not change. The index may make query faster. However, don't add useless indexes as it makes insertion a little bit slower.
The exhaustive syntax of the CREATE statement for the indexes is as follows:
CREATE[ UNIQUE] INDEX <INDEX name> ON <TABLE name> (<COLUMN name>[, <COLUMN name>]*);
The keyword UNIQUE indicates that all the group of values in the columns must be distinct.