SQL is yet another essential language for developers wishing to create data-driven websites.You want to get best results when writing SQL queries, minimizing errors and getting the best query performance when needed. So here are the few points to remember and apply to best results.

1. Creating Indexes Properly

Learning how to index properly is the best thing you can do to improve the performance of your SQL queries. Indexes allow for quicker access to the database in typical situations. Database novices often find indexes mysterious or difficult. They either index nothing or they try to index everything. Of course, neither of these approaches is right. With no indexes at all, your queries are likely to be slow. If you index everything, your updates and insert triggers will be inefficient.

What are Indexes?

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and is then referred to one or more specific page numbers.

General Syntax of creating indexes:-

CREATE INDEX index_name ON table_name;

There are few types of Indexes

1. Single-Column Indexes : - A single-column index is created based on only one table column.
2. Unique Indexes: - Unique indexes are used not only for performance, but also for data integrity. A unique index does not allow any duplicate values to be inserted into the table.
3. Composite Indexes : - A composite index is an index on two or more columns of a table. Its basic syntax is as follows.

CREATE INDEX index_name
on table_name (column1, column2);

Whether to create a single-column index or a composite index, take into consideration the column(s) that you may use very frequently in a query's WHERE clause as filter conditions.
Should there be only one column used, a single-column index should be the choice. Should there be two or more columns that are frequently used in the WHERE clause as filters, the composite index would be the best choice.

4. Implicit Indexes : - Implicit indexes are indexes that are automatically created by the database server when an object is created. Indexes are automatically created for primary key constraints and unique constraints.

2.Fetch only required data using Correct language

It is crucial for developers to use the proper language for the job. So basically here what we are trying to point is developer should use which select's required columns of table and efficiently.

For example:-

SELECT * FROM customer;

SELECT firstName, lastName, shippingAddress FROM customer WHERE customerID = 1001;

In the first example, the developer is selecting all columns and all rows from the customer table. In the second example, the developer is selecting only the first name, last name, and address from the customer table for a single customer with ID 1001. Not only does the second query limit the columns that are returned, it will also perform better.

Similarly, we would need sometimes to limit the rows using Limit clause.Example

SELECT name, price FROM products;

For instance, if you only want to display the first 10 records out of 50,000 on your website, it is advisable to inform the database about it. This way, the database will stop the search after finding 10 rows rather than scan the whole table:

SELECT name, price FROM products LIMIT 10;

Note : The LIMIT statement is available in MySQL and Postgresql, but other databases have ways to achieve a similar effect.

3. OR statement

The following example use the OR statement to get the result:

SELECT * FROM a, b WHERE a.p = b.q OR a.x = b.y;

The UNION statement allows you to combine the result sets of 2 or more select queries. The following example will return the same result that the above query gets, but it will be faster

SELECT * FROM a, b WHERE a.p = b.q
UNION
SELECT * FROM a, b WHERE a.x = b.y;

4. Understand Joins

Single table SQL select statements are rather easy to write. However, business requirements often dictate that more complex queries must be written. For example, "find all orders for each customer, and display the products for each order". Now, in this particular situation, it would be likely that there is a customer table, an order table, and an order_line table (the last would be to resolve a possible many-to-many record relationship).

For those who are slightly more familiar with SQL, it is readily apparent that a table join, actually, two table joins will be required for this query. So query can be:-

SELECT customer.customerID, order.order_id, order_line.order_item
FROM customer
    INNER JOIN order
        ON customer.customerID = order.customerID
    INNER JOIN order_line
        ON order.orderID = order_line.orderID;

Above is inner join example.

What are joins?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Types of SQL JOINs

  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

SQL Self JOIN

A self JOIN is a regular join, but the table is joined with itself.

5. Avoid cursors.

A bit of a no-brainer. Cursors are less performing because every FETCH statement executed is equivalent to another SQL SELECT Statement execution that returns a single row. The optimizer can’t optimize a CURSOR statement, instead, optimizing the queries within each execution of the cursor loop, which is undesirable. Given that most CURSOR statements can be re-written using set logic, they should generally be avoided.

6. Avoid  Wildcard Characters at the Beginning of a LIKE Pattern

Whenever possible, avoid using the LIKE pattern in the following way:

SELECT * FROM users WHERE name LIKE '%bar%';

The use of the % wildcard at the beginning of the LIKE pattern will prevent the database from using a suitable index if such exists. Since the system doesn’t know what the beginning of the name column is, it will have to perform a full table scan anyway. In many cases, this may slow the query execution. If the query can be rewritten in the following way:

SELECT * FROM users WHERE name LIKE 'bar%';

then the performance may be enhanced. You should always consider whether a wildcard character at the beginning is really essential.

7. Know Your DBMS Limitations

 Databases are powerful tools, however, they aren't without limitations. Oracle, SQL Server, and MySQL all have unique limitations on things such as maximum database sizes, the maximum number of tables, and others. Many developers unknowingly choose a DBMS solution for their project without planning or considering the later requirements of their database.

8. Use  of Order By

Order By, lets you specify an arbitrary sort function. Not quite sure how to use this yet? You can also do stuff like ORDER BY col1 + col2, which is cool. Example:-

SELECT col1, col2 FROM table_test
ORDER BY col1 + col2;