Open In App

SQL Query Complexity

Improve
Improve
Like Article
Like
Save
Share
Report

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 –

  1. O(1) — hash index on id, or cached result on id = 77 from previous query.
  2. O(n) – Do a full scan and look at each and every row and could not find any result.
  3. 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.

Last Updated : 17 Jun, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads