by Tseng H. K.
The article I read this week is “Speeding Up Your Website’s Database” by Paul Tero. The author is mentioning speed is always has been big issue when we design websites. Since April 2010, Google has announced they will include website speed category on their search ranking, website speed became even more important part of all websites. Google’s threshold for being fast-loading website is 1.5 second. The author talks about from briefly what database is. Next, what problems in database causes slow down a website. Finally, the author gives lists of solution to optimize the database, from very simple technic to complex solution using technical terms.
1. Indexing Database tables- The simplest way to boost up your website speed can be done by indexing your database tables. Columns that deal with a lot of WHERE condition with “=,” should be indexed.
2. Use full text index- If the search deal with a lot of text such as descriptions of products or article content, then you can use another type of index method called full text search.
3. Use optimize tools that comes with database software – If a table gets modified a lot, we can use database optimize tools to reduce in size and make more efficient. Author has tested himself a table has 100,000 orders, it was 31.2MB in size and took 0.2676 seconds to do SELECT * FROM ORDERS. After optimization, the size has reduced to 30.8MB and search time has reduced to 0.0595 seconds. The size did not change that much, but there is big difference in time wise.
4. Making sure to use the cache- Cache can help you boost up your speed massively. As point #3, author also did simulation himself. The query running time has been reduced from 0.0019 seconds to 0.0004 seconds (SELECT * FROM ORDERS WHERE CUSTOMERID=2).
In the Class, we have been talked about business relationship and rules. Not only business relationship and rules are important, speed is also fundamental in database management. I am definitely sure we are going to talk more about speed in later chapters.
The article was very helpful for me to read because it was directly related to my work. I had built an E-Commerce website year ago, and sales were not that bad in the beginning. But, from certain point of time, our website’s sales have decreased 25% to 50%. I spent days to find out reason for this matter. Later, I found out that as we add new products in our database, the website had become very slow, and it causes Google search engines to downgrade our website’s ranking. As a result, our products were not searchable in Google. It is very critical especially for E-Commerce sites to appear in the first page of search, it directly related to website traffic.
Tero, P (March 23,2011) Speeding Up Your Website’s Database retrieved from http://coding.smashingmagazine.com/2011/03/23/speeding-up-your-websites-database/