We all write sql queries and fetch the data from the database. Many a times, an inefficient query may pose a bottleneck on the production database’s resources, and cause slow performance for other users if the query contains errors. Most time, we write blind queries and get all the possible data, which is not even required or to be shown to the end user on the form or GRID. In most scenarios, there are few tweaks you can do to your sql queries to optimize for better good.
Database novices often find indexes mysterious or difficult. They either index nothing or they try to index everything.
2. Less is more, so select * [STAR] carefully
A common way of retrieving the desired columns is to use the * symbol even though not all the columns are really needed. If you only need a limited number of rows you should use the LIMIT clause (or your database’s equivalent). Take a look at the following code:
SELECT name, price FROM products;
SELECT name, price FROM products LIMIT 10;
3. Say no to correlated subqueries
A correlated subquery is a subquery which depends on the outer query. It uses the data obtained from the outer query in its WHERE clause. Suppose you want to list all users who have made a donation. You could retrieve the data with the following code:
SELECT user_id, last_name FROM users WHERE EXISTS (SELECT * FROM donationuser WHERE donationuser.user_id = users.user_id);
SELECT DISTINCT users.user_id FROM users INNER JOIN donationuser ON users.user_id = donationuser.user_id;
4. Avoid Wildcards
In SQL, wildcard is provided for us with ‘%’ symbol. We should be considerate for using wildcard, which 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. Below are few examples.
SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello%’;
SELECT * FROM TABLE WHERE COLUMN LIKE ‘hello%’;
SELECT * FROM TABLE WHERE COLUMN LIKE ‘%hello’;
Some of us might use COUNT operator to determine whether a particular data exist. There are many instances where we can make use of Exists. Example below:-
SELECT COLUMN FROM TABLE WHERE COUNT(COLUMN) > 0