Caching query results: PHP caching vs MySQL caching

[GetFree]

My site is a bit slow showing the main page so I thought caching query result in PHP will improve performace.
Then I read MySQL documentation and saw that MySQL does have a caching feature.
So... now I dont know if doing the PHP caching is worth the pain.

Would there be any noticed performace improvement if I cache query results in PHP, considering that MySQL is already caching the queries?

[coopster]

Welcome to WebmasterWorld, GetFree.

Each site and situation is different based on so many factors so a yes/no response here couldn't be justified. It may be your bandwidth as opposed to the HTTP server. Or perhaps it is the database server and queries as opposed to the HTTP server.

Caching may have it's place but so often we don't need to use it when we analyze our site structure and page delivery. If you are delivering a dynamic page from information in a database and the page creation is slow you might want to first optimize your queries. Have you created the appropriate indexes for the larger tables and/or more advanced queries?

[GetFree]

Yes, my site is generated from DB data.

I haven't checked if my queries are optimized yet, because MySQL is caching them. So the problem could not be that the queries are slow.

My doubt is actually whether PHP-cahing the queries will be a performace impact considering that the queries are being cached already by MySQL itself.

[edited by: GetFree at 5:55 am (utc) on Dec. 12, 2006]

[henry0]

First you need to let MySQL explain you what is happening when a query is executed
To do so
In front of a query add EXPLAIN
For ex: EXPLAIN select aaa from bbb where etc…
The output will supply you with very valuable info.
Read about it
HERE [dev.mysql.com]

Then you probably will need to add an index or indexes
to a table column used for example to perform a “where …. Statement”

To alter a table
Type:
ALTER table_name add index (column where a "where" is performed)

[GetFree]

I did the timing calculations for the page generation. It takes always less than 0.1 seconds to generate.
Nevertheless, the loading takes several seconds. That's what I see when loading the page in the browser.

I dont know what could be slowing down the page loading.

Any idea?

[mcibor]

So you mean, that if you measure time from start to the end of the page it takes 0.1s, but on the browser appears after seconds, then I presume it has sth to do with the bandwidth, not the php nor mySQL.

Do you have many pictures? How many kB your generated page has?

Michal

[henry0]

In order to get a good feeling about it
you should find how it goes when loaded from other people machine and from machines hooked to other LAN networks
Also did you try to look at it from your local machine through a local LAMP?

Did you by any chance load IE7 on your local machine?
If so check the security setting, the default performs too many checks.
Try to add the URL in the security area friendly sites

That’s another reason to look at it from other machines.