David Albrecht
David Albrecht

Reputation: 674

Can incorrect query results be explained by mysql cache?

EDIT: ok, this was all my error. I made a mistake resetting the least_prices values. The database still had the old wrong values. Whenever I clicked in my application on a product to get its id and look it up in the database an aftersave-hook would trigger a recalculation of the least_price, thus changing it to the new correct value. And I wrongly assumed looking up a product in the DB would change the cached value for least_price. I would delete this question, as it is very unlikely to help somebody else, but people have already answered. Thank you and sorry if I have wasted anybody`s time.

I recently set all values of one field (least_price) of my table products to new (higher) values with a php-script. Now I run this query:

SELECT Products.*
FROM products Products 
WHERE 
(
    Products.least_price > 240 
    AND Products.least_price < 500
) ;

and the result set contains some products with a new least_price value above 500. The result will show wrong (I am assuming the old) values for the least_price field. If I query a particular product with select product where id = 123 that happens to have a new least_price higher than 500, it will show the (newer/higher) least_price correctly. Next time I run the first above-mentioned query the result-set will be smaller by one product and the missing product is the one I queried individually.

Can this behaviour be explained by a query cache? I tried to run RESET QUERY CACHE, but I don't have the previleges to do that unfortunately with that hosting provider. Is there anything else that I can do to alert mysql that the least_price attribute has changed?

I am using mysql 5.6.38-nmm1-log on a x86_64 debian-linux-gnu machine with innodb 5.6.38

Upvotes: 0

Views: 671

Answers (1)

Barmar
Barmar

Reputation: 781716

No, this can't be due to the query cache.

The query cache doesn't cache row references, it caches the actual results that were returned. So it can't contain results that don't match the criteria in the query.

The cached result for a query is flushed if any of the tables it uses are modified. So it will never return stale data.

For full details of how the MySQL query cache works, see The MySQL Query Cache.

If the least_price column is indexed, your incorrect result could be due to a corrupted index, try repairing the table.

Upvotes: 1

Related Questions