# of queries versus performance issue with MySQL 4.x

[AKosygin]

With the query caching of MySQL 4.x, I always wondered how information is retrieved will have on the performance of the database. Presuming you will retrieve the same information either way, would many small queries be faster? Or would one large query with a bunch of JOINs be faster?

According to the MySQL documentation of the query cache, a change in the query statement will render that query a new query. Hence "SELECT * FROM table" is different than "select * from table" OR "SELECT column FROM table".

Keeping that in mind, a large query may involve small changes to it. Like when you are retriving a row, you may have to retrieve the poster's name, the contents of the post, the subject, and the avatar. One large query, where let's say only the name of the poster was changed and you run a query on that. Would that not cause the server to do a FULL table search again? Not just the poster name, but also the contents of the post, the subject, and the avatar, despite there are no change to those other parts?

On the other hand, if you seperate the queries with the above case, when PHP request for each item, the poster's name, the post contents, the subject, and the avatar, wouldn't only the poster's name be searched again while all the others are retrieved from cache?

So will one large query not take as much advantage of the cache as many small queries?

Wouldn't it because of the query cache size limit, a large query would not be cache due to its size? But many small ones will have a good portion of it cached, thus reducing query search load?

Sounds like, unless you are retriving less information when eliminating the number of queries, consolidating the queries in to one giant query may actually slow it down. (At least with MySQL 4.x). Anyone have any performance test they could cite?

[Xenon]

According to the MySQL documentation of the query cache, a change in the query statement will render that query a new query. Hence "SELECT * FROM table" is different than "select * from table" OR "SELECT column FROM table".

hmm according to a documentation i read, that's not correct.
IIRC it's not the values which are cached but the pointers to the resultrows, so it's actually the "WHERE" tag which decides if the cache can be used or not.
I'm not 100% sure, but according to that article i read about MySQL4 it's this way

[AKosygin]

According to here: http://www.mysql.com/newsletter/2003...000000108.html

As the query arrives to MySQL server it performs a textual comparison with the queries in the Query Cache. This means "SELECT * FROM a" and "Select * From a" will be recognized as different queries.

Because of this, that is why I am asking if there are any performance benchmarks or the like to see if one large query or many small one will be faster.