It is a better idea to optimize queries when you don't have time to change the system and still working on databases to bring the content. For example if you are using MySQL and the server loads are getting high when mysql works, you need to login to mysql and check the processes real time. Running SHOW PROCESSLIST command several times in a short time period will list the processes and queries that take some time.
AND and OR commands as well as unnecessary paranthesis () slows the query. You need to know boolean logic well and need to understand the code whether it returns true or false in the WHERE condition of SQL when you remove unnecessary paranthesis or ANDs or ORs. Sometimes a query delays the result a few seconds more and you may use at least 5 queries in a content management system.
One other important thing is to decide database connection persistant or not. According to my experience, if database server and the script accesing to it is inside the same server there is no need to connect persistant. But if there are too many connections, it may prevent sql server to say too many connections error. But if your database server is different machine than web script server persistant connections speeds up the connection and results a few seconds faster results.
If you have search mechanisms in your web site and serving a search page inside a lot of rows from database, and if you are using a pagination system like <previous 1-2-3-4-5.. next> links, you need to get the total number of rows inorder to create a pagination. A common method is using COUNT(id) command to get this number. But if your database is too large, this may result a high load in the database as well as web server. Two optimization methods are keeping the total number of rows in an other table each time the number of rows changes. But the frequency of change is big, it may increases the loads also. My solution is not to use COUNT(id) and counting rows by SELECT id with a limiting the query like LIMIT 1000. As the visitor will make a search to seek information, he/she needs to write a better search keywords or you must use relevancy methods in your returning results. There is no need to return over 1000 results for each query.
Murat BIYIKLI - 2008-03-15 19:31:04
