This is a fantastic article to see on ALA. In one of my past lifes I carried out a lot of performance testing and tuning web applications. If developers can make sure these simple things are done when the application is coded it will make everyone lives easier in the long term.
The difference to the user experiance will also be dramatic, the first time I ran a performanced tuned application I could not believe the difference it made.
I really enjoyed this article and could impliment a couple of the points listed – especially indexes on varchar type fields, which already shows some speed increases in one of my sites.
I really appreciate this article because it was a post more technical than usual. Most of the time, we discuss size reduction (removing exif on pictures, better compressions), number of files downloaded (css sprites, css group), cache and http issues so theses advices are welcome !
Webbies failing to think seriously about databases has to be one of the major problems of web application development. Unfortunately “toys” (and dangerous ones if incorrectly used) like MySQL and the associated literature don’t help. Articles like this can help to change this. Anyone starting out and looking for a free DBMS might one a look at PostgreSQL as it automatically creates indexes for any foreign keys that a table references, so you get performance + referential integrity. Add bound parameters to the client and you give the database chance to cache the query and you’re protected against SQL injection. Happy days!
I believe MySQL automatically creates an index when you create a foreign key constraint, which makes this advice even easier to follow, assuming you’re using a storage engine with foreign keys.
Another related topic that could help out the same audience is an introduction to normalization…
Michael, yes, I believe constrained foreign keys are indexed automatically. However, in MySQL the default storage engine in MyISAM, which doesn’t support constraints (as InnoDB does). In many applications, the relationships are only conceptual, which is why it’s so easy to overlook these things.
Copy & paste the code below to embed this comment.
darsh39
Yes this id true i found a huge difference in indexing the web.
This article help you to indexing a web…thanx for giving this type of articles which help other…Thanx Again
Great article. You have really illustrated the key things that are needed for indexing the web. In fact, these are the most important but oft-forgotten tweaks!
“Think of it this way—it’s like looking up the word “poultry” in the back of the cookbook instead of flipping through each page and putting a marker on all the chicken dishes.”
That summarizes the entire article in a nutshell. Good advice on how to keep databases running smoothly and efficiently, thanks.
It is not only me most of the webmasters and SEO experts would be thinking that indexing is Google’s job. But now I got what to do to make the search engines index the pages of the websites.
Database optimization is an often-overlooked aspect of web development. Many of the projects I’ve worked on pay absolutely no attention to it – to the extent that the only index on a given table is a primary key. Optimizations can have vast impacts on server performance; I recently built an ajax-based real time chat app that saw performance gains of about 60 times with some indexing and SELECT query optimizations. The “SELECT … USING” syntax is a godsend for hinting the database to use an indexed column in a multi-column select condition.
22 Reader Comments
Back to the Articlewelovenicethings
This is a fantastic article to see on ALA. In one of my past lifes I carried out a lot of performance testing and tuning web applications. If developers can make sure these simple things are done when the application is coded it will make everyone lives easier in the long term.
The difference to the user experiance will also be dramatic, the first time I ran a performanced tuned application I could not believe the difference it made.
Phil
AdriaanNel
I really enjoyed this article and could impliment a couple of the points listed – especially indexes on varchar type fields, which already shows some speed increases in one of my sites.
Christophe BENOIT
I really appreciate this article because it was a post more technical than usual. Most of the time, we discuss size reduction (removing exif on pictures, better compressions), number of files downloaded (css sprites, css group), cache and http issues so theses advices are welcome !
Charlie Clark
Webbies failing to think seriously about databases has to be one of the major problems of web application development. Unfortunately “toys” (and dangerous ones if incorrectly used) like MySQL and the associated literature don’t help. Articles like this can help to change this. Anyone starting out and looking for a free DBMS might one a look at PostgreSQL as it automatically creates indexes for any foreign keys that a table references, so you get performance + referential integrity. Add bound parameters to the client and you give the database chance to cache the query and you’re protected against SQL injection. Happy days!
carolinecblaker
So let me get this straight – having the same fields in the database yet declaring a few select ones as ‘index’ changes all of this? Really??
By the way, the site == great in expressionengine!
Michael Newton
I believe MySQL automatically creates an index when you create a foreign key constraint, which makes this advice even easier to follow, assuming you’re using a storage engine with foreign keys.
Another related topic that could help out the same audience is an introduction to normalization…
Lyle Mullican
Michael, yes, I believe constrained foreign keys are indexed automatically. However, in MySQL the default storage engine in MyISAM, which doesn’t support constraints (as InnoDB does). In many applications, the relationships are only conceptual, which is why it’s so easy to overlook these things.
forex
Thanks for a very informative article. The little things that make a huge difference in indexing the web… And they do make a difference.
Bjørn Enki
Thanks Lyle, great explanations and very informative!
darsh39
Yes this id true i found a huge difference in indexing the web.
This article help you to indexing a web…thanx for giving this type of articles which help other…Thanx Again
magicman24
that’s a really interesting article. I remember the days where it took about 30 minutes to index a page :)
Mike Hersh
imblogging.com
elearningflash
Sounds like building a good website is like building a good building
If the foundations are stable, it will last for a good while.
Maneet Puri
Great article. You have really illustrated the key things that are needed for indexing the web. In fact, these are the most important but oft-forgotten tweaks!
Thanks for sharing!
unbrandedc
“Think of it this way—it’s like looking up the word “poultry” in the back of the cookbook instead of flipping through each page and putting a marker on all the chicken dishes.”
That summarizes the entire article in a nutshell. Good advice on how to keep databases running smoothly and efficiently, thanks.
stuartmarsh
It was interesting that you mentioned frameworks. I use Django, I just checked the MySQL tables it creates, and it seems to index the foreign keys.
But it’s a lesson learned for me, as I’ve never really looked at database optimisation before. Time to start I feel!
Thanks
shapiawebdesigns
It is not only me most of the webmasters and SEO experts would be thinking that indexing is Google’s job. But now I got what to do to make the search engines index the pages of the websites.
JohnShuffle
Reading the acticle I hate to say that Google dictates our behavior forcing us to pay 24/7 attention if you do website promotion
Justen Robertson
Database optimization is an often-overlooked aspect of web development. Many of the projects I’ve worked on pay absolutely no attention to it – to the extent that the only index on a given table is a primary key. Optimizations can have vast impacts on server performance; I recently built an ajax-based real time chat app that saw performance gains of about 60 times with some indexing and SELECT query optimizations. The “SELECT … USING” syntax is a godsend for hinting the database to use an indexed column in a multi-column select condition.
Montmorency
Here is the translation of the article: http://interpretor.ru/sql_indexing
Thanks for the great article!
flash bannere
Interesting article! And this commentfield is really well designed :)
webdesign center
Great reading, very good article. Thank you
1smykke.dk
Helped me realize some very good points.