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.
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
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.
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.
- Query By Example (QBE)
- Useful CQL query in Cassandra
- SQL | SELECT Query
- SQL | Query Processing
- Query Execution in HP Vertica
- Structured Query Language (SQL)
- Relational Query Evaluation | Set 2
- Relational Query Evaluation | Set 1
- PHP | MySQL UPDATE Query
- PHP | MySQL Select Query
- What are the Best Ways to Write a SQL Query?
- PHP | MySQL Delete Query
- SQL query using COUNT and HAVING clause
- Neo4j Query Cypher Language
- Features of Structured Query Language (SQL)
- Query Optimization in Relational Algebra
- SQL query to find second highest salary?
- Join operation Vs Nested query in DBMS
- Additional Functions in CQL (Cassandra Query Language)
- Query to find 2nd largest value in a column in Table
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. 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.