After spending the past few months working in a SQL heavy application I've discovered there is a lot you can do as a developer to ensure your application stays optimized when calling stored procedures and running queries against your database.
Definitely, these optimization tips don’t guarantee that
your queries won't become your system bottleneck. It will require more
bench-marking and profiling to further optimize your SQL queries. However, the
below optimizations can be utilized by anyone and are good practices to know
when writing queries.
These have been a good learning experience for me and have helped eliminate a few pesky issues in my own application.
Wildcard
In SQL, wildcard is provided for us with '%' symbol. Using wildcard will definitely slow down your query especially for table that are really huge. We can optimize our query with wildcard by doing a postfix wildcard instead of pre or full wildcard.
#Full wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';
#Postfix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE 'hello%';
#Prefix wildcard
SELECT * FROM TABLE WHERE COLUMN LIKE '%hello';
That column must be indexed for such optimize to be applied.
*Doing a full wildcard in a few million records in a table is equivalent to killing the database.
COUNT VS EXIST
Some of us might use COUNT operator to determine whether a particular data exist
SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0
Similarly, this is very bad query since count will search for all record exist on the table to determine the numeric value of field 'COLUMN'. The better alternative will be to use the EXIST operator where it will stop once it found the first record. Hence, it exist.
Wildcard VS Substr
Most developer practiced Indexing. Hence, if a particular COLUMN has been indexed, it is best to use wildcard instead of substr.
#BAD
SELECT * FROM TABLE WHERE substr ( COLUMN, 1, 1 ) = 'value'.
The above will substr every single row in order to seek for the single character 'value'. On the other hand,
#BETTER
SELECT * FROM TABLE WHERE COLUMN = 'value%'.
Wildcard query will run faster if the above query is searching for all rows that contain 'value' as the first character. Example,
#SEARCH FOR ALL ROWS WITH THE FIRST CHARACTER AS 'E'
SELECT * FROM TABLE WHERE COLUMN = 'E%'.
Data Types
Use the most efficient (smallest) data types possible. It is unnecessary and sometimes dangerous to provide a huge data type when a smaller one will be more than sufficient to optimize your structure. Example, using the smaller integer types if possible to get smaller tables. MEDIUMINT is often a better choice than INT because a MEDIUMINT column uses 25% less space. On the other hand, VARCHAR will be better than longtext to store an email or small details.
Indexes
Index your column is a common way to optimize your search
result. Nonetheless, one must fully understand how does indexing work in each
database in order to fully utilize indexes. On the other hand, useless and
simply indexing without understanding how it work might just do the opposite.
Symbol Operator
Symbol operator such as >,<,=,!=, etc. are very
helpful in our query. We can optimize some of our query with symbol operator
provided the column is indexed. For example,
SELECT * FROM TABLE WHERE COLUMN
> 16
Now, the above query is not optimized due to the fact that
the DBMS will have to look for the value 16 THEN scan forward to value 16 and
below. On the other hand, an optimized value will be
SELECT * FROM TABLE WHERE COLUMN
>= 15
This way the DBMS might jump straight away to value 15
instead. It's pretty much the same way how we find a value 15 (we scan through
and target ONLY 15) compare to a value smaller than 16 (we have to determine
whether the value is smaller than 16; additional operation).
NOT Operator
Try to avoid NOT operator in SQL. It is much faster to
search for an exact match (positive operator) such as using the LIKE, IN, EXIST
or = symbol operator instead of a negative operator such as NOT LIKE, NOT IN,
NOT EXIST or != symbol. Using a negative operator will cause the search to find
every single row to identify that they are ALL not belong or exist within the
table. On the other hand, using a positive operator just stop immediately once
the result has been found. Imagine you have 1 million record in a table. That's
bad.
Index Unique Columns
Some databases such as MySQL search better with column that
are unique and indexed. Hence, it is best to remember to index those columns
that are unique. And if the column is truly unique, declare them as one.
However, if that particular column was never used for searching purposes, it
gives no reason to index that particular column although it is given unique.
Max and Min Operators
Max and Min operators look for the maximum or minimum value
in a column. We can further optimize this by placing a indexing on that
particular column, We can use Max or Min on columns that already established
such Indexes. But if that particular column is frequently use, having an index
should help speed up such searching and at the same time speed max and min
operators. This makes searching for maximum or minimum value faster. Deliberate
having an index just to speed up Max and Min is always not advisable. It’s like
sacrifice the whole forest for a merely a tree.
Primary Index
The primary column that is used for indexing should be made
as short as possible. This makes identification of each row easy and efficient
by the DBMS.
String indexing
It is unnecessary to index the whole string when a prefix or
postfix of the string can be indexed instead. Especially if the prefix or
postfix of the string provides a unique identifier for the string, it is
advisable to perform such indexing. Shorter indexes are faster, not only
because they require less disk space, but because they also give you more hits
in the index cache, and thus fewer disk seeks.
Limit The Result
Another common way of optimizing your query is to minimize
the number of row return. If a table have a few billion records and a search
query without limitation will just break the database with a simple SQL query
such as this.
SELECT * FROM TABLE
Hence, don't be lazy and try to limit the result turn which
is both efficient and can help minimize the damage of an SQL injection attack.
SELECT * FROM TABLE WHERE 1 LIMIT 10
Use Default Value
If you are using MySQL, take advantage of the fact that
columns have default values. Insert values explicitly only when the value to be
inserted differs from the default. This reduces the parsing that MySQL must do
and improves the insert speed.
In Subquery
Some of us will use a subquery within the IN operator such
as this.
SELECT * FROM TABLE WHERE COLUMN IN
(SELECT COLUMN FROM TABLE)
Doing this is very expensive because SQL query will evaluate
the outer query first before proceed with the inner query. Instead we can use
this instead.
SELECT * FROM TABLE, (SELECT COLUMN
FROM TABLE) as dummytable WHERE dummytable.COLUMN = TABLE.COLUMN;
Using dummy table is better than using an IN operator to do
a subquery. Alternative, an exist operator is also better.
Utilize Union instead of
OR
Indexes lose their speed advantage when using them in
OR-situations in MySQL at least. Hence, this will not be useful although
indexes is being applied
SELECT * FROM TABLE WHERE COLUMN_A =
'value' OR COLUMN_B = 'value'
On the other hand, using Union such as this will utilize
Indexes.
SELECT * FROM TABLE WHERE COLUMN_A =
'value'
UNION
SELECT * FROM TABLE WHERE COLUMN_B =
'value'
Hence, run faster.
