MYSQL :: The Query Cache Does Not Return Stale

MySQL Query Cache: Should I Cache Small, Simple Queries Or Only Complex Ones?
Query cache works great for long, complex queries, but should I also be caching the simplest of select queries.

For example let's say I had a table with 100 records and I needed to select something using a unique key:

SELECT name FROM products WHERE id = 3
Is caching the above pointless, especially in terms of wasted cache memory, considering how basic it is?

Query Cache
I have some problems with i suppose is the "Query Cache".
I have mysql server version 5 installed.

I have an applicattion with works with odbc 3.51 driver.

The problem is... the effect of query cache when i work with 2 or 3 concurrent sessions in my application.

I'm doing this with a session (session 1):

select * from table...
update table...

In the other session (session 2) i do the SAME select:

select * from table...

I can't see at session 2 the changes of sentence update of the 1st session!! Only i can do this it's work the session and begin.

I supossed this a query cache, i dessactivate it i have tried. Know i have:

query_cache_type = OFF
query_cache_size = 0

Query Cache
I was playing around with the query cache. However, after I set global query_cache_size = 20480; and show variables like 'query_cache_size'; the size shown is still 0. Also tried to change the my.ini file. but still 0. The query_cache_type is set to 1.

Query Cache Help
I have a VB front end that works against a MySQL backend. There is a search module that displays matching records and allows you to edit and delete records. If records are deleted and the user than does another search, the results still display the deleted records even though they are no longer in the table. I am assuming this is a cache issue. I don't want to eliminate the cache in fear of performance degrade but would like to give the user the option to refresh which would clear the query cache. Has anyone done this before or does anyone have a better solution?

Query Cache?
So I'm fairly new to mySQL. I've developed a site in php, and I was wondering how I could speed up the queries. Essentially, every time a page is loaded, it has to run through a database of around 2,500 rows, and it has a bit of a lag every time that occurs. Is there any way to cache the database to speed up performance after the initial load?

Query Cache
Is there any way to see which queries are cached?

Should I Use MySQL Query Cache?
The majority of my website's bottlenecks occur due to the database overhead. This is because I run a user community website, where users can send messages, post comments, read/write forum topics, and view profiles.

I have been studying to solve how to speed up the http and the website, however I was also thinking about using caching techniques for the database as well (as opposed to only PHP and HTTP).

Therefore, since MySQL has it's own Query Cache system, would this be effective. I understand that it would work in a system that has a lot of SELECT queries with little UPDATE and INSERT procedures, but for a user community website which consists of lots of changing data, would it be effective?

When and where should Query Caching be used? My Server has alot of RAM and space, so what else should I look out for?

MySQL Query Cache
Is there a way to save or export the query cache in MySQL? I would like to keep the queries to run again when I change the data in my tables.

Query Cache Adjustments
The query cache is brilliant (in theory), but I find in practice it rarely does much.

The problem is that the cache is flushed after every table insert/update. The benefits of this are obvious (keep query results up to date), but I feel having the ability to adjust how often the cache is updated would be immeasurably beneficial.

If only I could have it updated every 5/10/50 inserts/updates (depending on level of table access).

This could lead to a boon to performance with little problem of data getting stale.

Is there anyway way to do this?

Query Cache - What Gets Pruned?
When the Query Cache is full and something has to be pruned, how is the decision to prune a query made? Is it first in-first out, or least recently used-first out? Can I manually set a priorty on what is removed from the cache first? Is there a "Never Remove From Cache" flag?

Query Cache Issues
I have a MySQL server running 4.0.12 on a 12 CPU Sun U4500 with 12GB of
memory.

With the query cache running, this machine would noticeably "hiccup"
(just stop responding to requests) every so often and wouldn't squeeze
more then 1000 queries per second or so as a result (heavily mixed OLTP
work, with a lot of SELECT's and almost as many UPDATE's).

Without the Query Cache (and no other tweaking), I'm now at over 2100
at the same time of day.

Is there a problem with the query cache that causes MySQL to be far
less scalable then just straight MySQL/InnoDB?

Are there any thoughts on this? Is there a resource specifically for
high performance/high load MySQL implementation/usage?

Understanding Query Cache
when using the search on one of our sites, it takes 4-5 seconds to return a result but if I immediately search for the same thing again it returns instantly due to the Query cache which is great. What seems strange is that 15-30 seconds later it ignores the cache and runs the same query again for a result. Is that normal? It seems like the Query Cache should maintain the query and result until the query cache runs out of memory and starts dumping the oldest records.

Redirecting Query Cache
I am trying to find out how to redirect the mysql query cache to a hard drive.

My goal is to make it usefull again after a restart.

ON DUPLICATE KEY UPDATE And Query Cache
After bludgeoning my head against the table for a bit, I figures out what the problem was that had been plaguing me.

When using a INSERT INTO ... ON DUPLICATE KEY UPDATE ...
statemtent, it seemed like the update was not taking place.
After viewing the webpage in question, (and doing the manual select statement), the old data was being returned.

I couldn't figure out why, until FINALLY, I added 'RESET QUERY CACHE' manually after the statement. Then the correct updated info was returned.

Apparently the query cache is not flushed after using the above ON DUPLICATE KEY UPDATE statement, like it does with other insert or updates.

What do I do now. Just not use that statement? Id like to avoid locking tables if possible. And I cant have the website showing the outdated info.

Query Cache: Simple Question
I just have a simple question about MySQL QQ.

What I want to know is: Is the query cache only setup to apply to queries that stay the same?

PHP

"SELECT one,two,three FROM table LIMIT 1"

(Standard query, no WHERE clause, works the same for all visitors)

But a Query like this then doesn't stay the same

PHP

"SELECT one,two,three FROM table WHERE id='".$id."' LIMIT 1"

Since the ID value depends on a php variable, thus is different each time, then the query is in fact not the same.

Is the use of the query cache still beneficial in a case like this?

Weird Query Cache Numbers
I'm running 4.0.18, and the queries in cache variable grew from about 2K
to 30K in a matter of hours when we hardly used the database at all.
Has anyone seen something similar? (The cache size was set to 100M and
cache limit to 5M -

Benchmark Command And Query Cache
I'm trying to optimize my database, and have read about the Benchmark command.

Here is one that I am playing with:
SELECT BENCHMARK(100,'SELECT COUNT(*) FROM table');

I assume this syntax is correct because I don't get any errors. However, this also doesn't return any errors:
SELECT BENCHMARK(100,'Any Invalid SQL Statement');

Also, I am having a hard time understanding how BENCHMARK can be effective regardless of syntax. I'm guessing the first time a query is ran, it will be stored in the QUERY_CACHE. Subsequent iterations of the benchmark command would basically be pointless (because the query is cached and not actually ran against the DB).

Thoughts?

Testing Speed And Query Cache
I'm trying to test performance on some queries with a million of registers on my table, using various keys.

The problem is that the first time i run the query, it takes about 4/5 seconds, but after that it takes about 0.02 seconds. I know that query cache can be flushed with reset query cache, flush query cache, flush tables also modifing some caps at the sql query, but i'll never take 4/5 seconds again.

for example:
select * from table where id=2 order by index_field desc; 4/5 seconds.
select * from table where id=2 order by index_field desc; 0.02 seconds.

select * from table where id=3 order by index_field desc; 4/5 seconds.
select * from table where id=3 order by index_field desc; 0.02 seconds.

etc, etc...

Skip-new Disables Query Cache?
skip-new is explained as follows in the manual: "Don't use new,
possibly wrong routines." (4.1.1 mysqld Command-line Options). Is
there a list of the routines that will be disabled when using this
command-line option?

The manual also states the following: "concurrent_inserts If ON (the
default), MySQL will allow you to use INSERT on MyISAM tables at the
same time as you run SELECT queries on them. You can turn this option
off by starting mysqld with --safe or --skip-new." (4.5.7.4 SHOW
VARIABLES).

So when i was trying to turn off concurrent insterts I used -skip-new
which also disabled query caching. It took me a little while to figure
this out. I'm using skip-concurrent-insert now. Should this section of
the manual be updated?

System Info:
Windows 2000 SP3
mysqld-nt.exe Ver 4.0.13-max-debug for Win

Query Cache Not Working: MySQL 5 / Windows XP
I just installed MySQL 5, and its running great except that the query cache is not working, despite the configuration excerpt from my "my.ini"
below:

query_cache_size=5M
query_cache_limit=2M
query_cache_min_res_unit=4096k
query_cache_type=1

From the MySQL command line, if I enter: SHOW VARIABLES LIKE
'have_query_cache';

I get:
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+

Also, doing a: SHOW STATUS LIKE 'Qcache%'; gives me:

+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5234168 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 450 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+

I figure the "Qcache_free_blocks" and "Qcache_total_blocks" values of 1
are creating the problem; it's basically telling me that there are no
(okay, 1) free blocks to put my query. However, these values never
change.

The query cache is not vital to me, but it does improve performance, and
I'd like to get it going....

Query To Return Two Count()s
I'm trying to figure out a better way of doing this:
SELECT
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 1
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event1,
(SELECT COUNT(*)
FROM GuestEvents
WHERE EventID = 2
AND EventStatus = 'y'
AND UserID = 'bob')
AS Event2
As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit:

SELECT COUNT(EventID=1) AS Event1,
COUNT(EventID=2) AS Event2
FROM GuestEvents
WHERE EventStatus = 'y'
AND UserID = 'bob'

Query To Return Database Name
Can anybody suggest a query which returns the db name, I need to use it at run time.

Return Speed Of Query
Is there a quick and easy way to return the speed of the query within its results?

Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time.

However, if an event didn't happen say for 2AM then 2AM isn't shown in the return.

How can I get 2AM to show as a row, but with a count of 0 (zero) ??

Here is my qeury...

select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;

Default MySQL Return Value For Query
I'm writing a multilingual web site where not all content will necessarily be multilingual. I have a languageid and want to retrieve the content for that language. However, if that content doesn't exist, I would want to retrieve content for the default language.

My ideal query would be something like:

SELECT content FROM page_content WHERE pageid=$pid AND languageid=$userlang IF NO RESULTS WHERE pageid=$pid AND languageid=$defaultlangid

I imagine my made- up 'IF NO RESULTS' directive does not exist. Is there any way to get that kind of functionality from one query? Since I will be having queries all over the site to retrieve different kinds of localized things (image tags, html code, textual content), it would be awesome if I could figure out a way to consolidate.

Maybe there is a way to sort results from this query:

SELECT content FROM page_content where pageid=$pid AND (languageid=$userlang OR languageid=$defaultlang) ORDER BY .... ?

I'm also not sure if the nested boolean will work.

Need Query That Will Return Firstnames With X Characters
I have spammers who post on my website. They like to use things like Home-loan-refinancing as their first name when registering. So I need a query that will return all rows where the firstname field contains 20+ characters or something like that.

How Can I Return (x,y) Query Values To Create A Graph?
Im trying to build a graph of values from a counterTable. (Eg - get a list of how many times a page was requested in a week period with each y value being a day).

If the table looks like this...

Lost Connection To MySQL Server During Query Socket Error On Read. WSAGetLastError Return 10054($2746)
We just migrated our MySQL database from a NetWare server running version 5.0.21 to a openSUSE box running version 5.0.45.

Everything ran fine for a couple of days, then clients began losing their connections and creating new ones without the old threads being terminated. This behavior is the same for the Delphi client we use to access our database as well as the MySQL Administrator tool.

I've tried adjusting connection_timeout, but overall the settings for this server are the same as the previous one that ran without issues for a year.

Using A Limit Clause, But Return The Number Of Rows Of The Query Without The Limit
I have heard of a cool feature that mysql provides a way to return the number of rows of an sql statement that contains a LIMIT as if the LIMIT had not been there.

I search the mysql manual, but could not find anything.

Return Only 3 Photos From 3 Specified Galleries In One Query (was "Is It Possible?")
Let me start from the beginning.... I have a table that lists photo details, with a foreign key to link them to a gallery. Lets say I have 20 photos altogether and 4 galleries, (for simplicity sake, 5 photos per gallery).

My question is:

Is it possible to query the table to get it to return only 3 photos from 3 specified galleries in one query? (So returning 9 photos altogether, 3 from each gallery) Rather than querying the table 3 times with a limit of 3 and merging the results.

Query Table A And Return Records Not Found In Table B.
Given table A and table B.  Table A is a master table and table B is a list of authority or reference which is fed by selecting records from table A (via an UI I wrote in PHP).

The interface is working fine but I am left with one problem.    If I need to edit my table of authority, the source table (table A) should return ONLY entries not previously selected.

That said, if the A.key is in B.key, records from table A matching B.key should be omitted.

I could do this by looping through the query results from table A and querying table B, if no match, show A.key.  This I think is a bit antiquated and possibly too involved.

Is there a joint query command I can use?

Can I Cache My Whole Db?
I would like to know if I can cache my whole database. I have a couple of databases. All of them are very small, less then 5MB. Is there a way to cache the whole database in memory? What other ways are there to improve performance?

Cache
My web site is pulling from several tables that don't change all that often
and seems to be an ideal candidate for query caching. I tried to enable it
by copying the my-medium.cnf to /etc/my.cnf and restarted the service. From
my query editor I typed SHOW VARIABLES LIKE 'QUERY_CACHE_TYPE' and it
returns ON which is exactly what I want.

I then executed several queries from my site but none of them are being
cached. They're just plain SELECTs in most cases, any ideas why the DB won't
cache them? Do they have to be executed several times first?

When I try and check Qcache_queries_in_cache I get an empty set, likewise
for Qcache_not_cached.

Splitting Key Cache
In mysql 4.1, there are new ways to save different index files into
different key_cache segments. The idea is to remove problems with
key_cache locking if another process is accessing it.

As this involves maintenance, etc....

Question: Has anyone seen any measurable improvement in doing this,
vs. just creating a huge key_cache and being done with it.

Key Cache Buffer
It grows and grows and causes the mySQL client to throw an Out of Memory error. The only recourse I have is to reboot.
There must be a way to flush this without a reboot and I can't find it in the manuals.

MySQL Cache
I'm developing a Java application using MySQL v.3.23
via JDBC. When I change something in the database
from one computer, I cannot see the modification from
different computer. Even if I exit the application
changing the data, the second application is still
unable to see the changes.

Does Mysql Cache Joins?
will mysql still cache the first... umm... "quert" where it's selecting the forum name in this queru

SELECT
forums.forumname,
threads.threadname
FROM
forums, threads
WHERE
forums.forumid = 1
AND
threads.threadid = 7

or would it be better doing two queries, and knowing it'll cache the forums one?

SELECT
forum_name
FROM
forums
WHERE
forumid = 1
^^ Will get cached

SELECT
topic
FROM
threads
WHERE
threadid = 7
^^ might get cached after a few times the query has been called?

I'm quite new to "query cache"
I didn't know mysql did this
and now I'm trying to improve some of my apps, so I thought I'd askl =]

Connection Seems To Cache Data
I open MySQL Manager and connect to my database.

I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

Does Mysql Cache Results?
I'm doing some web development and have mysql (3.23.x) installed
to test with (win2k, but I don't think this question is particularly
platform specific).

I have a reasonably complex query which I am trying to optimise.
When I first startup mysql, and perform the query, it can take
10 or so seconds. However, further subsequent queries (sorting
by the same column) return in a fraction of a second. This is true
even with new WHERE or LIMIT clauses.

Is mysql creating a 'temporary' index and caching it? Is the original
10 second query the actual time for my query to execute? I'm finding
it difficuly to optimise my query as I'm not sure exactly which times
are correct.

Connection Seems To Cache Data
I then insert one record in to an InnoDB table in MySQL (4.0 and 4.1
alpha hosted on XP running mysqld --console).

I then retrieve the record successfully in MySQL Manager using the
existing connection. This indicates that commit is running
successfully, (I have seen this on the database trace too).

I then try to retrieve the record (through a small MySQL/TomCat
application). Sometimes it is retrieved successfully, and sometimes an
empty record set is returned.

If I reduce connection pooling (on the connection script (in
components.xconf))to min 1 max 1 connections, the record is retrieved
successfully every time.

This seems to me to indicate that existing connections created for my
TomCat app are searching on an old (maybe cached?) version of the
database. However, when by chance I get the same connection I used to
insert the record, the record is retrieved successfully.

However, the record is retrieved more often than not even when there
are 10 connections pooled, although I understand that connections are
not round-robbined and some are used more commonly than others,
supporting my suspision.

This is the only insert that uses transactions in my app, and it works
fine with Oracle. All other inserts (not using transactions) work fine
on both database servers (and both MySQL 4.1.1-alpha and 4.0.16).

Mysql Cache While HotCopy Runs
What I am looking for is if MySQL has the ability to cache Transactions while the database is locked (i.e. doing a Mysqlhotcopy or possible starting replication) whether in a buffer or possibly temporary table and then to commit this data to the database once the tables are unlocked.

Obviously it would be better if the buffers were able to be defined at differing sizes.

Return Zero Instead Of Nothing
I want this query to return a zero on the days of selection that have no record.

SELECT thedate, thecount FROM thetable WHERE DATE_SUB('$Today',INTERVAL 6 DAY) <= thedate AND thedate <= '$Today' && ID = $ID ORDER BY thedate ASC

So it works great if all the days in the selection have a value. If one of the days in the sequence has no record, then it skips it, and thus no value is presented.

The question is, how do I get mysql to give me a zero if it doesnt find anything?

Return A Value
$title = mysql_query("SELECT prod_title FROM products WHERE prod_id = '$array['spec_prod_id']'");

will $title equal a value or just that string?

Return Other Db Value
SELECT `id` FROM `users` WHERE `username` = (the username that user is logged in as)

2 things here,

1. $_SESSION['username'] can be called (in php, i don't know about mysql i would guess not) to get the username you are logged in as

2. i want the result of my query to be set a a variable such as $id

Return A Row
How can I return a row when the condition is not met for example simplified

SELECT Name,SUM(Cost) AS T FROM DB WHERE Name="+Name +" GROUP BY Name

this works fine but I ned it to return Name and Zero Cost if Name does not exist

How Do I Return The First Record In DB?
Table name: Fabrics

fabric_id
fabric_name
fabric_color

I would like to retrieve only the first record in the database i.e. the record with the smallest fabric_id number.

I thought I could do a SQL statement like the following, but it doesn't work.

SELECT MIN(fabric_id) as id, fabric_name, fabric_color FROM fabrics