MYSQL :: Query Cache Not Working: MySQL 5 / Windows XP

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?

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.

Working In A Windows Directory...over The Web
Is there a way to set up a database so it reads a windows directory?

Like the W:/ drive? Or have a function that moves files from W:/ to C:/?

and then have this functionality over the web?

Where do I start? I want this to be simple but all the resources I find are complex or more toward website stuff. Iâ€™d like to develop the frontend with PHP and have it interact with VB as well.

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?

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.

Simple Mysql Query Not Working
I have a simple table set up listing stockists and there details, included in this table I have a name column and a website column.

I wish to select the name and the website of the stockists only if a website has been entered into the website column.

Here is the query I have tried to use
Quote:

SELECT name, website FROM stockists WHERE website IS NOT NULL ORDER BY name ASC

This query is returning stockists with no website as well as the ones with websites and I donâ€™t know why!

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

The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified.

From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed."

I would like to know what is the definition of "when tables are modified".

does it mean?

1. new records inserted into table?
2. records gets updated?
3. table structure is modified by using alter table command?

Query Not Working In 5.0 That Worked In 4.0
I've created a development site for my website that uses mysql 5.0.18 and my live site uses 4.0.16. I have a query I can run without problems on the live site but is giving me errors on the development site and I was hoping someone might know what the problem is. It must be some change to the syntax standard I suppose. Thanks for your help!!!

1054 - Unknown column 'p.products_id' in 'on clause'

select count(p.products_id) as total from products_to_products as pp, products p, products_description pd, manufacturers m left join specials s on p.products_id = s.products_id where pp.products_id=&#56703;' and p.products_id = pp.related_products_id and pd.products_id=p.products_id and p.products_status = Ƈ' and pd.language_id = Ƈ' and m.manufacturers_id = p.manufacturers_id

ANDs In Query Not Working
I am working with this query:

SELECT * FROM reviews WHERE gID = 'nothing' OR gID = '825' OR gID = '864' OR gID = '632' OR gID = '880' OR gID = '839' OR gID = '534' OR gID = '680' OR gID = 'x' AND type = 'online' AND status = '1' ORDER BY RAND() LIMIT 0, 10

With everything going on here, the most important is that only rows with status=1 are selected. However, I am getting some rows with status=0 in my results. Is there something wrong with the ANDs in my query?

Update Query Not Working: SQL Error
i'm trying to do an update such as ...

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.

Query Of Gmdate Not Working... Is My Syntax Correct?
$query="SELECT distinct(id),subject,view, postdate FROM threads where gmdate(Y-m-d H:i:s, postdate + 3600) >= date_sub('".$current_date."', interval 7 day) order by view desc limit 0 , 5 ";

I tried to get my postdate to compare with the current date in order to display the most view topic in last 7 days in my page...

however, the above coding is not working...

Query To Display The Rows As Colums Is Not Working!!
I want a select query to get two columns. but i need to get these columns one below the other that is consider that i have a table student in that i have 2 columns name mark.
Now i want the result as

Name Raj Rina Tina
Marks 80 90 70

I tried the --vertical option of mysql and G option in the query.

for example when i tried

select *from studentsG

it displayed

*********** 1. row ***********
name: Raj
marks : 80
********** 2. row ************
name: Rina
marks : 90
********** 3. row ************
name: Tina
marks : 70

but i want to display

Name Raj Rina Tina
Marks 80 90 70

Order By Not Working Correctly, Query Problem?
I am using the following query to get a list of anime names and related info from my mysql db. As anime are sometimes known by several names (the actual japanese kantakana/kanji, the romanji translation and the english word). So the info_anime table has all the info about the anime and the info_animename just has the relevent animeid and the name.

The query below is ment to check all the names in info_animename and join them to info_anime.

SQL
SELECT n.*, a.* FROM info_animename AS n, info_anime AS a
WHERE
a.animeid = n.animeid
ORDER BY ".$_GET[orderby]." ".$_GET[order]."

Now all the names are displayed properly with the various info but if you look here:

Live Anime - Anime List

You will see that the names are not in alphabetical order, for the most part they are just anime with several names are not working correctly if you look at the bottom of the page you will see:

Yakusoku no Basho Kumo no Mukou followed by
Beyond The Clouds, The Promised Place

Which are the same anime under different names.

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 =]

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.

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.

Query Behaving Differently In Windows And Linux
I have a query to find the blog entries that are most commented:

SELECT blog.*, COUNT(blogcomment.id) AS cnt FROM blog, blogComment WHERE blog.ID=blogComment.BLOG_ID GROUP BY blogComment.BLOG_ID ORDER BY cnt DESC

The query works fine on my development machine running Windows mysql 5.0.22 but on my Linux server running the same version of mysql (5.0.22) I get the following error:
#1054 - Unknown column 'blogcomment.id' in 'field list'

Any ideas why it works differently on the two servers? Suggestions on a better written query is ofcourse welcome too

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.

Working Of Mysql
can any one explain me in details how mysql engine works

PHP Not Working With MySQL
Just starting out and I have PHP, Mysql, and Apache installed. PHP and MYSQL dont seem to be working together. Everything seems to be running fine and I edited the httpd.conf in order to allow apache to process php requests.

This is what I am doing when I start mysql...
c:/net start mysql
c:/mysql/bin/mysql -u root -p
Then I log in and it accepts the log in.

When I create a sample php program like "Hello", it works.

But when I do a simple sql connect:

<?php
$conn = mysql_connect("localhost","root","tntboy");
echo $conn;
?>

I get nothing! Nothing at all comes up on the screen.
Any ideas? Why is it not connecting to the database?

Still Cannot Get MySQL Working
I have the MySQL server running. I have installed MyODBC. I cannot get either the Migration Toolkit or the MySQL Administrator to connect. I've turned off the Windows Firewall and put exclusions in Norton to allow port 3306 and MySQL.

With the MySQL Administrator I get an error message of Cannot Connect to MySQL Server on localhost. With the Migration Toolkit I get this error log: Code:

Mysql Not Working
im trying to install bugzilla (im on rh9) ive gotto the point where you run ./checksetup.PL.
Thing is its saying it cant connect to the database - but the database is there and the settings n the config files are right.

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.

Working With Images In MYSQL
How do I store and retrieve images in MYSQL database, and display this image on a web page, using PHP.

MySQL Not Working After Reboot
I just installed MySQL 4.0.18 on my Win2K computer. Once it installed, I
ran the WinMySQLAdmin program and everything worked fine. The little green
light was on, and I could connect to my databases.

However, as soon as I restarted my computer, the green light changed to a
red light, and since then I've been unable to connect to my databases. When
I open WinMySQLAdmin, the "Server" group box on the Environment page is
completely empty (it used to have lots of server information in it).

Mysql Fulltext Key Not Working
i created a table that uses full text search and the full text field is: FULLTEXT KEY client_search (name,city,country)

in my mysql query i tried running this query but i got no results although there is a value in the field called "john" as the name for the client.

selet * from client where match (name,city,country) against ('john')

the above query returns 0 results set, although i have a client called john under name.

Code:

XAMPP / MySQL / Not Working
I installed XAMPP on my MAC Powerbook G4, running OS X 10.4. It all installed fine and I can see it in the applications menu. In there is also a script "mamppstart", when I click this I get the following message:

Starting XAMPP for MacOS X 0.5...
XAMPP: Starting Apache with SSL (and PHP4)...
XAMPP: Starting MySQL...
XAMPP: Starting ProFTPD...
XAMPP for MacOS X started.

I'm assuming that's good and means that everything has installed and running, however I try to locate MySQL in the Terminal and get nowhere. Also when I click my MySQL preference pane it says there is an error.

MySQL REGEXP Not Working
I have a PHP script that uses MySQL (v3.23.53-max-nt) REGEXPs,
and these seemed to be working fine until it needed to use the
condition:

msg_text REGEXP 'status: (failed)'

I knew there was a record containing this string. But the script
didn't return it, until I had reduced the condition to:

msg_text REGEXP '(failed)'

The problem seems to be caused by the colon, despite this not
being a reserved character (as far as I can tell from the MySQL
manual).

Also, the manual says that preceding special characters by ''
causes them to be treated as normal characters. So why in
that case does MySQL complain about 'unbalanced brackets'
when I tried running:

msg_text REGEXP '(fail'

Luckily, I was able to work round these problems by using
a LIKE clause instead.