Optimizing Your SQL Queries
There are numerous web developers all around everywhere.
Some master in the art of creating graphic rich designs and some in creating dynamic systems using server side scripting languages and databases.
I will talk about server side scripting experts.
With due respect to all experts; i have came across very small number of programmers who are actually capable of creating large portals.
Not because they are not good at programming but due to the fact that they do not pay proper attention to database design.
When creating large portals its very essential to pay attention to the database design otherwise with increase in visitors and data in database, the website would start loading slowly and may also crash sometime in future.
Here are some tips for those programmers:
Will post more in future.
Some master in the art of creating graphic rich designs and some in creating dynamic systems using server side scripting languages and databases.
I will talk about server side scripting experts.
With due respect to all experts; i have came across very small number of programmers who are actually capable of creating large portals.
Not because they are not good at programming but due to the fact that they do not pay proper attention to database design.
When creating large portals its very essential to pay attention to the database design otherwise with increase in visitors and data in database, the website would start loading slowly and may also crash sometime in future.
Here are some tips for those programmers:
- Indexes - Indexes are most important when you are building a large database.
When you declare columns as primary keys, they are automatically created but in other cases you have to create them.
Create indexes on columns which you think you are going to use repeatedly in "where" clause of your queries.
Another good candidate of an "index" can be the columns name that you repeatedly use in "on" part of your join clauses.
But do not over index.
that can again slow down the processes, so create indexes on only those columns which you are going to use repeatedly in your whereclauses and joins.
- Use multiple row insert statements.
whenever a record is inserted, the index is updated.
So its better to insert more than one record if possible and then index updates at once.
- Use subqueries rather than joins if you do not have to output field values from second table.
Subqueries are relatively faster than joins.
Will post more in future.