15 ways to optimize your SQL queries

Document directory

• Like this post? Share it!

Previous Article was on 10 ways to destroy a SQL database
That sort of teaches you what mistakes should company might make on their
Database that will eventually lead to a database destroy. In this
Article, you will get to know 15 ways to optimize your SQL queries.
Always ways are common to optimize a query while others are less obvious.

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
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, a 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 ).

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 wildcardSELECT * FROM TABLE WHERE COLUMN LIKE '%hello%';#Postfix wildcardSELECT * FROM TABLE WHERE COLUMN LIKE  'hello%';#Prefix wildcardSELECT * FROM TABLE WHERE COLUMN LIKE  '%hello';

That column must be indexed for such optimize to be applied.

P.s: doing a full wildcard in a few million records table is equivalence to killing the database.

Not Operator

Try to avoid not operator in SQL. It is much faster to search for
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.

Count vs exist

Some of us might use count operator to determine whether a participant 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

Most developer practiced indexing. Hence, if a participant column has been indexed, it is best to use wildcard instead of substr.

#BADSELECT * FROM TABLE WHERE  substring ( COLUMN, 1, 1 ) = 'value'.

The above will substr every single row in order to seek for the single character 'value'. On the other hand,

#BETTERSELECT * FROM TABLE WHERE  COLUMN like 'value%'.

Wildcard query will run faster if the above query is searching
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 like 'E%'.

Index unique Column

Some database 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 Fig column was never used for searching
Purposes, it gives no reason to index that participates 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 particle ColumnMisleading
We can use Max or min on columns that already established such indexes.
But if that maid column is frequently used, having an index shocould
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. Its like sacrifice the whole forest for a merely a tree.

Data Types

Use the most efficient (smallest) Data Types possible. It is
Unnecessary and sometimes dangerous to provide a huge data type when
Smaller one will be more than sufficient to optimize your structure.
Example, using the smaller integer types if possible to get smaller
Tables. A smallint is often a better choice than int because a smallint
Column uses 25% less space. On the other hand, varchar will be better
Than text to store an email or small details.

Primary Index

The primary column that is used for indexing shoshould be made as short
As possible. This makes identification of each row easy and efficient
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 TOP 10 * FROM TABLE

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 has CES 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
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'UNIONSELECT * FROM TABLE WHERE COLUMN_B = 'value'

Hence, run faster.

Summary

Definitely, these optimization tips doesn't guarantee that your
Queries won't become your System Bottleneck. It will require much more
Benchmarking and profiling to further optimize your SQL queries.
However, the above simple optimization can be utilize by anyone that
Might just help save some colleague rich bowl while you learn to write
Good queries. (its either you or your team leader/Manager)

Like this post? Share it!

Orginal URL: http://hungred.com/useful-information/ways-optimize-sql-queries/