How can I reduce the amount of times I have to send a query to the sql database?

Question

PHP version 4 & 5.
MySQL version 4.

I was wondering after looking at the amount of queries smf (simple machine forums) sends to the database. If I coded the forum the amount of queries I would have sent would be way over a hundred while smf does around 10. How can I greatly reduce the times that I need to send queries to the mysql database?

Answer 1

First of all it is important to note, limiting the number of queries to your SQL server isn't necessarily the best way to improve performance. In most cases you're better off having a lot of very simple targeted queries ten less large complex queries.



However, there may be a situation depending on your Web-server and sql server hardware and software configuration that could make reducing queries of the SQl server a priority.



Assuming that is a case I'll cover a few things.



1. If you use PH P's mysqli extension to query your sql server you can combine multiple statement's. See this article for more info.



http://www.php.net/manual/en/mysqli.overview.php



However, from a performance perspective I'm not sure this will have any differnt functional effect from several queries listed one after another in PHP.



2. If I were in the situation I'm assuming you're in ( need to reduce sql queiries.). Take a look at all the data you may need to query for a user of your site, put it in one large sql query and assign it to a signel array veriable to be retrieved when the user needs it. IN most cases this will be very wastefull of resources but if the sole goal is to reduce queries of the SQL server this will do it.

Answer 2

Most SQL languages including MySQL allow you to send multiple queries in the same request by appending a semicolon to each statement. This would minimize the amount of calls needed and speed things up.

Answer 3

The number of queries is not necessarily a accurate indicator of the overall efficiency of your app - a better one is the amount of information transferred beteen the server and the client. A dozen queries can be far more efficient than a single one if each of the dozen only passes back a single row while the single one passes back 12,000. I/O speed is often the major bottleneck of any process.

Answer 4

actually, I don't think you are allowed to use semicolons(;) between statements in PHP - no semicolons allowed. I suggest you try to use JOIN and INNER JOIN (and other types of JOIN) statements where possible, and depending on the version of MySQL you have, I believe version 4.1 or 5.1 allowed you to do nested SELECTs. for instance,

WHERE id IN (SELECT 1002, id from users WHERE...)

is a valid option.

might I suggest you get the visibone.com MySQL syntax chart?



INNER JOINS and other JOINs speed up queries a lot. use them where you can. study them, use them, read up on them, practice using them. get used to them.



another tip that should help is to use indexes in your database on columns that are frequently used to lookup things by.

Answer 5

the only table on your FROM is pupil. in case you decide on for to consult with a column in yet another table on your question, it has to look interior the FROM (and could be related to the different tables in some way, the two via a connect or by using the the place clause.)

Answer 6

as well as using joins where possible as mentioned above. You could return multiple tables at a time



SELECT * FROM somewhere WHERE x = y <-- table 1



SELECT * FROM somewhere WHERE x = y <-- table 2



then you have to step through the tables as well as the rows to read through all the results.