Re: cmysql (was: Package proposal)

Hello Mika, Tuesday, October 12, 2004, 3:44:53 AM, you wrote: >> Actually caching itself is really couple hundreds lines of code. But >> main benefits is certain query caching and automatic cache validation. >> This allows just forget that we actually use queries cache and just >> create code as for native 'DB'. MT> Regarding the userland implementation of query cache for MT> MySQL. How does it differ from the query cache natively MT> provided by MySQL? Ok, i will try to explain. 1. Queries cache is supported by MySQL 4.0.1 and higher. Probably this argument may sounds strange for some developers, but if you mainly run scripts on servers or accounts rented in hosting companies it might be a problem for you as Ensim and even Plesk7 use MySQL 3.23.58 by default. 2. By default caching mechanism is turned off as default value 'query_cache_size' is '0' so it depends on hoster wether cache is actually enabled or not. Even if cache is enabled developer needs to modify SQL queries: a) SET SESSION query_cache_type = DEMAND - to allow caching only explicitly defined by 'SELECT SQL_CACHE' prefix queries. b) Add 'SQL_CACHE' to all required queries. This will results in problems with SQL syntax for MySQL 3.xx.xx And even if we done all above it would not guarantee us good results for server with hundreds virtual hosting accounts running hundreds different queries per minute. The source of problems there is: a) Queries cache size is limited on server. b) Most of developers will not use 'SET SESSION query_cache_type = DEMAND' and 'SQL_CACHE' prefix to minimize number of queries stored in cache. So, server's cache on hosting server will be completely full of trash queries from scripts on other hosting accounts and all efforts are lost in vain. 3. We will still send queries to MySQL. It is not so good for two reasons. a) If we use some dedicated server we will spend time for network communications. b) As PHP4 MySQL API does not support queries preparations, server will check query syntax each time. 4. Even we are lucky and we get our query cached we still need call 'mysql_fetch...' methods to retrieve data from server and process it to form 'getAll', 'getOne', 'getAssoc' and other 'get...' functions result value. When 'userland implementation of query cache' is used it allows to avoid these problems. a) we do not depend on MySQL version b) we do not depend on hoster, cache memory size and other competitive queries. c) we do not send queries to MySQL d) we store result of 'get...' query in cache, so we do not need any data fetching from server and it further processing. -- Best regards, Maxim Antipin (ITScript CEO) mailto:max@itscript.com