In today™s time, website speed is one of the major issues that every business is facing. And, it has become more essential right from the time Google has decided to use it in the search rankings. However, the main point of discussion is basically on improving server settings, reducing file sizes and optimizing JavaScript and CSS. Apart from this, the speed with which the pages are generally put together on the server is also an important concern. Many websites keep the information safe in their database and make use of language such as PHP or ASP in order to extract it and turn it into HTML to send to the Web browser. So, even if your home page speed is brought down to 1.5 seconds, you may irritate the customers in case your search page takes too long to respond.
Here, in this article, we are discussing these sorts of issues and will also provide you some simple steps to speed up your website by optimizing the database. Starting with some common knowledge, we have included some complex techniques at the end to give you a brief insight.
Meaning of Database and SQL
In general terms, database simply means an accumulation of tables of information like a list of customers and their orders. Talking about the database for a blog, it consists of tables for posts, users, categories and comments. WordPress database includes all the above features and also comes with a few other starter tables. A typical e-commerce database consists of tables for customers, categories, products and order items. Apart from this, the database also has many other uses such as for customer relations, content management, invoicing, accounts and events but in this article, we will discuss more about e-commerce and blog website.
Database also refers to the software containing various data such as I really need to upgrade my database or My database crashed while I was having breakfast. Some of the most famous database software include Microsoft SQL Server, Oracle database 11g, Microsoft Access 2010, MySQL and PostgreSQL.
SQL which stands for structured query language is basically a language that is utilized to ask and tell a database things SELECT last name FROM customers WHERE city=’US’. This is known as database query as it inquires the database regarding data. Aside from this, there are several other database statements such as UPDATE for adding something new in the existing data, CREATE TABLE for creating tables and many others.
How A Database Can Affect Your Site™s Speed?
When a website is designed, it performs very smoothly but as it becomes older, some sluggishness on certain pages with complicated bits of functionality can be noticed. For instance, if you wish to show, Customers who bought this product also bought¦ at the end of a page of products. In such a scenario, you will need to extract this information from the database following these steps:
- Begin with the latest product
- Check out how many times the product has been added to the shopping basket
- Look for the customers ordering those products
- Search for the other orders made by them
- Determine the overall content of those orders’ basket
- Get the complete details of those products
- When the full process is completed, then display the item that appears the most
All this can be done in one single massive database query or you also have the option to split it up into different queries. Either way, your website might run efficiently if your database has less number of products, orders and order items.
There might be other reasons also for a website to slow down like the server might run low on memory or disc space, the server sending several emails or churning at some other task, software, hardware faults and many others. Or it might have become the most populous website. In the next two sections, we will discuss in detail about the speed.
Is It My Database?
With the advent in technology, many ways have turned up by which you can easily analyze the speed of your website. Methods such as the developer tools in Google Chrome, Firebug plug-in for Firefox and Yahoo YSlow can be of great help for checking the website™s speed. Apart from this, there are various websites like WebPagetest where you can simply enter a URL and it will detect it from the chosen location. These tools mark up the diagram of varied resources such as JavaScript files, HTML images and CSS utilized by your page and also indicates the time it takes to load.
How To Time A Database Query In PHP And MySQL
If you find out that your database is slowing down your website, then it is of utmost importance to check out that from where the delay is coming. Here, we will discuss a few timing functions and then make use of it to time all the database query ran by a page. The code discussed below is precisely for PHP and MySQL but the process can also be utilized for database-driven website.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | function StartTimer ($what='') { global $MYTIMER; $MYTIMER=0; //global variable to store time //if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') return; //only show for my IP address echo '<p style="border:1px solid black; color: black; background: yellow;">'; echo "About to run <i>$what</i>. "; flush(); //output this to the browser //$MYTIMER = microtime (true); //in PHP5 you need only this line to get the time list ($usec, $sec) = explode (' ', microtime()); $MYTIMER = ((float) $usec + (float) $sec); //set the timer } function StopTimer() { global $MYTIMER; if (!$MYTIMER) return; //no timer has been started list ($usec, $sec) = explode (' ', microtime()); //get the current time $MYTIMER = ((float) $usec + (float) $sec) - $MYTIMER; //the time taken in milliseconds echo 'Took ' . number_format ($MYTIMER, 4) . ' seconds.</p>'; flush(); } |
With the StartTimer command, the timer gets started and allows you to print whatever you are trying to time. The second line indicates to check the IP address. This can turn out to be very useful if you are doing this on a live website and do not wish to show the timing messages to everyone across the globe. Next line gives you the option of uncommenting the line by eliminating the initial //, and replace this 127.0.0.1 with your IP address. With the StopTimer command, the timer gets stopped and shows the time taken.
There are many modern websites specially well-programmed open-source ones that contain several PHP files but inquire the database in only limited places. Go through all the PHP files of your website for mysql_db_query or mysql_query. Various software development packages like BBEdit are packed with such features that make it capable to perform searches like this. Apart from this, if you have knowledge of Linux command line, you can simply try this:
1 | grep mysql_query `find . -name *php` |
You may also come through some command like this:
1 | mysql_query ($sql); |
In case of WordPress 3.0.4, this is on line 1112 of the file wp-includes/wp-db.php. You can simply copy and paste this function on the top of this file given above or in any PHP file included by every single page. After doing this, add the timer before and after the mysql_query line. It will appear this way:
1 2 3 | StartTimer ($query); $this->result = @mysql_query( $query, $dbh ); StopTimer(); |
Summary on the final outcome
The method used above provides only the comparative results. At that point of time, if your server was overloaded, then all the queries would slow down than working normally. But, you should be capable enough to determine as to how long a fast query takes on your server or diagnose the slower ones and then the slowest ones. You can run this test over the course of an hour or day (but not immediately- go through the section below about the database cache) ensuring that you do not get a fluke.
What else it can be?/
If you find that your database queries are not slow, but the development of your web page is, then you must have poorly written code. In such a scenario, you can place the timer statements on top around bigger blocks of code in order to see where the delay is occurring. It might be that you are roaming around 10000 full rows of product information in spite of displaying only 20 product names.
Index database tables
The above experiment might have amazed you by displaying the number of database queries that are running on a page of your website and helped you to recognize the slow queries.
Lets go through some easy improvements for speeding the things up. For this purpose, you require to run database queries on your database. Various server administration packages come with phpMyAdmin for this purpose. Preferably, you can also upload thing such as phpMiniAdmin to your site as this PHP file allows you to view your database and run the queries. For running the queries, you require to enter the database name, password and user name. In case, you have no such details, you easily get it from your website™s configuration file if it is having one.
What all to index?
In order to analyze which columns to index, it is essential to gain some knowledge as to how your database is being utilized. For instance, if you think that your website is generally used to view categories by name or events by date, then in such case, these specific columns should be indexed.
Process of indexing
Making use of phpMyAdmin or phpMiniAdmin allows you to view the structure of each database table and look whether the required columns are indexed or not.
In phpMyAdmin, click the table™s name and go to the bottom where Indexes are listed. When it comes to phpMiniAdmin, you need to click Show tables that is at the top and then sct for the table in question. This process will help you to analyze the database query required to recreate the table and will also include indices at the bottom such as
1 | KEY 'orderidindex' ('orderid'). |
In case the index does not exist, you can add it without any hassle. In phpMyAdmin, underneath the index, it states, Create an index on 1 columns; click Go here, enter a unique name for the index, select the column on the next slide and then press Save.
Security and Back-ups
It™s essential to make a back-up of the complete database before making any changes in the database tables. This can be done by utilizing phpMyAdmin or phpMiniAdmin by clicking Export. Store the back-ups in a safe place especially if it contains full customer details. Apart from this, you can also utilize the command mysqldump in order to back up a database via SSH:
1 2 3 | mysqldump --user=myuser --password=mypassword --single-transaction --add-drop-table mydatabase > backup`date +%Y%e%d`.sql |
The above script indicates the security threat as it makes it simpler for anyone to steal all of your data. phpMyAdmin is generally offered securely through your server management software but on the other hand, phpMiniAdmin is a single file that is easier to upload and forget about. Thus, you may wish to password-protect it or remove it after usage.
Optimizing Tables
In order to optimize the data, MySQL and different types of database software come fully equipped with built-in-tools. If your tables keep on changing, then you can run the tools on a regular basis in order to make the database tables smaller and more effective. As it take some time to run and block other queries from running on the table at the time of optimization, thus it would be the best option to do this at a non-busy time.
In order to optimize a table, run database statements like phpMyAdmin or phpMiniAdmin:
1 | OPTIMIZE TABLE orders; |
Eliminating the queries using JOINs
Probably in the control area and management of your e-commerce website, there might be a list of the orders with the names of the customers who made them. This page might consist the query like to find all the completed orders with a status indicating whether an order has been completed or not:
1 | SELECT * FROM orders WHERE status>1; |
And for each single order, it might look up for the customer™s details:
1 2 3 4 | SELECT * FROM customers WHERE id=1; SELECT * FROM customers WHERE id=2; SELECT * FROM customers WHERE id=3; etc |
For instance, if this page shows 100 orders in one time, then it may require to run 101 queries. And in case, if these customers start looking up their delivery address in distinct table, or search for the total charge for all their orders, delay in time will start adding up. This process can be done faster by merging the queries into one by making use of JOIN.
See how a JOIN appears for the queries above:
1 2 | SELECT * FROM orders INNER JOIN customers ON orders.customerid = customers.id WHERE orders.status>=1; |
Wrapping Up!!!!
Ranging from simple to tricky process, we have included several techniques for improving the performance of your database. Though, a well-designed website should incorporate most of these techniques, but generally they are overlooked. So, hope the above given advice turn out to be beneficial for you and provide you an overview of what is available and what not.
