SQL Query Complexity

Structured Query Language (SQL) is the language that is used to query Relational Databases. A typical Relational Database consists of tables, where each table has rows and columns. Every column has data of particular type. Factor dependent on SQL query complexity.

Example –

select * 
from employee 
where id = 77

There are three different way that query could find the result –

O(1) — hash index on id, or cached result on id = 77 from previous query.
O(n) – Do a full scan and look at each and every row and could not find any result.
O(log(n)) – Sort the id and do a binary search.

The complexity of the query totally depends upon the SQL engine how it will process the query. If our employee table has 100000000000 rows of data and we need to find out the row where employee id is 77. If we scan the whole table then it would take a long time. If we sort the table and do a binary search on this table then we need about 36 lookups (log base 2 of 100000000000 is ~36) to find out our value. But to sort the table would take a while. It totally depends upon the optimiser of the sql engine.

I would recommend this if you want to know more about SQL engine query processing.

Things to keep in mind while writing good query :

Limit your result –
When you can not avoid filtering down your select statement, then you can consider limiting your result. You can limit your result by using limit, top
Example –
```
select TOP 3 
from employee
```
```
select * 
from employee limit 10
```
Don’t make the query to be more complex. Try to keep them simple and efficient.
Example –
Consider the following query:
```
select * 
from employee 
where id = 77 or id = 85 or id = 69
```
So, you can replace the operator by using IN
```
select * 
from employee 
where id in (77, 85, 69)
```
select * to be avoided especially when you have a join as at least one column twice which is waste full of server, database and network resources.
Understand aggregates stop thinking about loops.
Aggregates like – count, avg etc.

Article Tags :

DBMS

PostgreSQL

SQL

DBMS-SQL