php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #61161 MySQL SUBSTRING function disables caching
Submitted: 2012-02-19 00:43 UTC Modified: 2012-03-02 09:36 UTC
Votes:1
Avg. Score:5.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:1 (100.0%)
Same OS:1 (100.0%)
From: nick at ihighteam dot com Assigned: uw (profile)
Status: Closed Package: mysqlnd_qc (PECL)
PHP Version: 5.3.6 OS: FreeBSD
Private report: No CVE-ID: None
 [2012-02-19 00:43 UTC] nick at ihighteam dot com
Description:
------------
The mysqlnd_qc extension performs very well, except when certain queries are used.  When the mysql SUBSTRING function appears in the query string, regardless of the function parameters or column size, mysqlnd_qc chooses not to cache the results.  Just by removing the SUBSTRING function, one can see caching works again.  I discovered this by observing the cached results taking the same time to return or longer as uncached results.

Reproduce code:
---------------
This query is cached and returns the results 2 times faster:

SELECT content FROM articlestext ORDER BY articleid DESC LIMIT 18

This query is not cached:

SELECT SUBSTRING(content, 1, 300) FROM articlestext ORDER BY articleid DESC LIMIT 18




Expected result:
----------------
The query SHOULD be cached with results much faster than a regular query.


Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2012-02-27 11:10 UTC] uw@php.net
Automatic comment from SVN on behalf of uw
Revision: http://svn.php.net/viewvc/?view=revision&revision=323575
Log: Test for  #61161 MySQL SUBSTRING function disables caching
 [2012-02-27 11:11 UTC] uw@php.net
-Status: Open +Status: Verified -Assigned To: +Assigned To: uw
 [2012-03-02 09:36 UTC] uw@php.net
-Status: Verified +Status: Closed
 [2012-03-02 09:36 UTC] uw@php.net
Sorry, but your problem does not imply a bug in PHP itself.  For a
list of more appropriate places to ask for help using PHP, please
visit http://www.php.net/support.php as this bug system is not the
appropriate forum for asking support questions.  Due to the volume
of reports we can not explain in detail here why your report is not
a bug.  The support channels will be able to provide an explanation
for you.

Thank you for your interest in PHP.

Hi Nick,

its been a while we worked on QC, thus both Andrey and I didn't recall that this behaviour is on purpose. You need to set mysqlnd_qc.cache_no_table to enable caching. 

Assume you have a SQL statement like:

  SELECT some_column, RAND() AS _rand FROM some_table

It is very likely that you do not want this query to be cached. It is likely that your application depends on the random value. Or, think of:

  SELECT some_column, NOW() AS _time FROM some_table
  SELECT SLEEP(1) FROM DUAL

Thus, by default, we do not cache such statements. This feature is not very well documented. However, its mentioned at http://de2.php.net/manual/en/mysqlnd-qc.configuration.php#ini.mysqlnd-qc.cache-no-table .

If mysqlnd_qc.cache_no_table = 0 no query will be cached which has at least one column in its resultset for which MySQL reports no table. In other words a column like "1 AS _one" or "SOME_FUNCTION() AS _func" or "SOME_OTHER_FUNCTION()". There is currently no built-in functionality to set a black or whitelist to refine the behaviour of the global mysqlnd_qc.cache_no_table setting. It should be possible to change behaviour and implement such a feature using an user defined cache handler callback for the cache decision.

Would you like to see some sort of black/whitelisting built-in to the plugin? I assume you would. However, I wouldn't promise it for 1.1 because the old QC code base needs serious refactoring before we add any new features.

Hope that helps!

Ulf
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC