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 –

  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.

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.

Article Tags :
Practice Tags :

Be the First to upvote.

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.