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?
View Complete Forum Thread with Replies
Related Forum Messages:
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?
View Replies !
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?
View Replies !
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....
View Replies !
Complex Queries Versus Multiple Simple Queries
I am constructing a database to contain information about stories posted on my site. Information included will be things like title, author(s), genre(s), story codes, synopsis, etc. I worked out that storing this information properly, so that it can all be searched on, could take as many as ten tables. My question is this: Is a single complex query really better (more efficient for the server) than multiple simple queries? In other words, I may need the information for as many as 25 or even 50 stories for a single page. Is it better to get all of the information out of a single, massive, complex query, or is it acceptable to get the information essentially one story at a time, which could mean 25 or 50 simple queries...?
View Replies !
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?
View Replies !
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
View Replies !
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?
View Replies !
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?
View Replies !
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.
View Replies !
Query Cache :: 'query_cache_size' Is 0
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.
View Replies !
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?
View Replies !
Query Cache For Every User
enabled Query cache using root user... I see the query_cache_type and query_cache_size variables correctly set. I also get correct status values for Qcache_inserts and so on. But when I login as another user (the user with which my web app logs in to DB) and see these values, the query_cache_size is still zero...and the Qcache_inserts don't get updated either...
View Replies !
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
View Replies !
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 -
View Replies !
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?
View Replies !
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...
View Replies !
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?
View Replies !
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.
View Replies !
Query Cache / Synchronising Two Tables
I have a downloads table consisting of 230,000 records. This table is constantly searched in up to two varchar(255) columns, which appears to be causing a great deal of problems during peak times. From looking at my slow query log, this seems to have an adverse affect on a range of queries that fetch and sort rows of common download data. I've been looking at some solutions to the problem and I've come to the assumption that I should cache common search queries and try to utilize the MySQL query cache so that those more common queries are read from memory. However, my download table is constantly updated with download counts and ratings so switching on the query cache is pretty much useless as it currently stands. Would it be possible/sensible to replicate the downloads table within the same database and then synchronise the two tables every few hours? Could anyone explain how I could do this? Is the query cache even suitable for rows of commonly fetched data? Also, with regard to a search cache, would anyone know where I can find more info/advice on how to build a search system that uses a cache system for common keywords, or give me a basic idea of how a system like this might work?
View Replies !
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.
View Replies !
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.
View Replies !
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 =]
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
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.
View Replies !
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?
View Replies !
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.
View Replies !
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.
View Replies !
Cache Thrashing?
First and foremost, the Qcache_free_blocks value just will not stay down. As I write this, Qcache_free_blocks is at 16363; Qcache_total_blocks is at 60107. This server is set up as a slave. However, every few hours, the database will have a replicated UPDATE query stuck on "end" status for anywhere from 5-8 minutes (during which time no other queries, even selects, are allowed to execute). Today I tried watching the Qcache status variables while it was stuck in "end" state and noticed that Qcache_queries_in_cache was rapidly decreasing, Qcache_free_memory was rapidly increasing, and Qcache_free_blocks was sometimes increasing. We allocated a gig to the query cache. It doesn't seem like it should take 5-8 minutes to clear out the query cache. Anyone have any ideas? I plan on marking some of our "process" queries as SQL_NO_CACHE, which should minimize how often the DB gets locked in end state, but it seems like it shouldn't be blowing away the query cache for 8 minutes after a single query (the query is usually replicated from the main server but preceded by similar updates to the same table(s) from the main server). In one 5-8 minute cleanup session, it went from having Qcache_queries_in_cache 618,000 to 163,115.
View Replies !
Deleting SQL Cache
I'm currently trying to optimise some of my queries. When I write them and execute them the first time, some of them take a few seconds, but submitting that query again returns the results immediately. Is there a way to make the server "forget" about previous queries so that all of my queries are done on a "clean slate". Or some sort of command which clears the cache buffer memory or whatever it's called.
View Replies !
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).
View Replies !
Fulltext Cache System?
I have a table of about 170,000 rows. Its basically a lyrics database in this format : __________________________ | ID | Song-artist | Lyric-text | --------------------------------------- When i do a query that searches song-artist, it take about 4-12 seconds to full match it. Its their a better way to lay this out, to make it much faster? Or will i have to implement a cache system?
View Replies !
Mysqlorary Tables And The Cache
The documentation says that the cache does not work with mysqlorary tables.. for instance: select sum(blah) from atable where date between '2008-01-01' and '2008-11-01' will be cached.. but create mysqlorary table results select sum(blah) from atable where date between '2008-01-01' and '2008-11-01' will not involve the cache..
View Replies !
Select Hash Over Resultset For Cache Validation?
I have a table with a lot of values that rarely change and I would like to cache these values in a file at the client side. I would like to select a hash over the WHOLE resultset (NOT per row) - something like select SHA1(*) from some_table; This would enable me to check if an update is required without transmitting all the values.
View Replies !
MySql Complex Queries With Joined Tables.
I am using MySql 5.0, I've created a database with few tables. Being a beginner I dont understand the joining syntax in MySql. I need help in quering and joining these tables. Configuring my Database: 1) How can I change a CurrentDate field with currentDate() or Date_Time() prebuild functions to show the current timestamp field? 2) Can I change the timestamp date format on the database? I am trying to add and retrieve the timestamp field on an ASP.NET application. Secondly, I need to query for the following scenarios: 1) List all rooms numbers with its Capacity, Room type and resource names; 2) List all reservations made for rooms on 'CreationDate' 3) When reserving a room on a start and end time, how can I query or check the database if the start and end time is not booked, ie in my asp.net application I need to check if there is no time conflict whilst booking a reservation of a room based on start time and end time. 4) List all reserved rooms on selection of current date with capacity and room numbers. 5) How can I lock a table when a booking transaction is being done simultaneously by another person? 6) Having inserted a userid and 'password' into mysql tables, is password a reserve word? How would I insert and retrieve forgotten passwords by email? 7) How can I create and call the above statements in stored procedures? This my Database: Code:
View Replies !
2 Simple Mysql Queries
have problems with 2 simple queries and i dont know how to solve them. plz help me. first one looks like this: 1.What’s the average length of the movies released in 2006? And which movie comes closest to that average length? (search upwards) i wrote 2 separate queries: select avg(length) from film where release_year = 2006; and the second one: select title, length from film where release_year = 2006 order by length; i have to make this into one query. how do i do that? what should i write? the second query looks like this: One of the customers comes into the video store and he needs your help to find a children movie with 2 words to solve a word puzzle. In the puzzle the second word has 8 letters. He already knows that the 4th letter is “E” and the last 2 letters are “N” and “D”. Help him to find the movie i wrote: select title from film where title like ('% ___E__ND') and film_id in (select film_id from film_category where category_id in (select category_id from category where name = 'Children')); but the problem is that it doesnt return me a result. this query contains everything but the things are not in order. plz help me!
View Replies !
Complex Queries On Many-to-Many Relationship
I'm building a system that involves performing fairly complex queries against a set of member data. The member data consists of answers to a series of questions, each question is multiple choice, with the possibility of the user selecting multiple answers to a question. As such, Answers to Member accounts is a many-to-many relationship. My table structure currently looks like this: (with extra, un-related fields removed) Code:
View Replies !
Combining Two Complex Queries
I'm dealing with hierarchical data using the modified adjacency list model. In implementing the ability to move entire subtrees, I've come up with the following two queries:
View Replies !
Complex Searching And Multiple Queries
I have a database of a list of companies and descriptions where users can login and see reports from companies they are subscribed to. 1) I want users to be able to search for a keyword and have it search the ENTIRE database for the keyword and return all rows. 2) For those returned rows, I want to cross-reference the permissions table and have it: ---A) return the company name and description if they are subscribed ---B) print that they are not subscribed to this company. Here is a simple version of the database: user ==================== id | username ==================== 1 | bob 2 | john reports ======================================= id | description | company ======================================= 1 | Green | Crayola 2 | Green | Bic 3 | Blue | Papermate permissions =========================== user_id | report_id =========================== 1 | 1 1 | 2 2 | 1 I need to put these two statements together(shown below). I need to have it search the DB and return all rows, then check to see if the person has access to that company. If they do, show it to them, if not print that a company was found but they are not subscribed to it. So if John was logged in and searched for "Green" his results would return two results, one would show that he could see the report from "Crayola" and it would also show that the company "Bic" was found, however he is not subscribed. Search results: 1) Crayola - click here to see the report 2) Bic - you are not subscribed to this company. Here is a look at the MySQL I have been trying to use: PHP $query1 = "SELECT * FROM `emt_report` WHERE `company` LIKE '%$P_search%' OR `description` LIKE '%$P_search%' OR `market1` LIKE '%$P_search%' OR `market2` LIKE '%$P_search%' OR `market3` LIKE '%$P_search%' OR `market4` LIKE '%$P_search%' OR `market5` LIKE '%$P_search%' OR `market6` LIKE '%$P_search%' OR `location` LIKE '%$P_search%' OR `date_year` LIKE '%$P_search%' OR `date_month` LIKE '%$P_search%' OR `source` LIKE '%$P_search%'" PHP $query2 = "SELECT u.id , u.username , p.user_id , p.report_id , r.id , r.company , r.description , r.market1 , r.market2 , r.market3 , r.market4 , r.market5 , r.market6 , r.location , r.date_year , r.date_month , r.source , r.video , r.audio , r.pp , r.execsum , r.report_url , r.exec_url FROM user as u INNER JOIN user_reports as p ON p.user_id = u.username INNER JOIN emt_report as r ON r.id = p.report_id WHERE u.username = '$username'" From above, how can i have search by query1 and then have it cross reference query2?
View Replies !
Yet Another Complex Php/mysql Query
im trying a select, compare, update sota query thingy 'm' jig.. what i need to do is: i have a 2 rows in a table(table=users) called gcount and credits both rows are numeric. im trying to build a query that a) updates gcount by gcount = gcount + 1 b) if gcount = 5 reset gcount to 0 update credits = credits+1 so basically what im after is a query that adds 1 to the g count every time but if the gcount reaches 5... it is reset to zero and credits is reset to + 1 is this too much to ask from one query? am i totally bonkers and you have no idea what i mean?
View Replies !
Complex Limit/offset Queries Causing Dirty Data
Thanks for reading. I have a rather complex page which is ultimately meant to return a list that's a small subset of products I sell. The page currently returns the list, but with an additional few items that should not be in the results. The code for the page works in 3 segments. 1st an sql statement links 2 tables (one table tells me the name of the product, and the other table tells me what genre I've decided the product falls in). The 1st statement counts the number of products which are not displayed on the final page, or finds the the offset value. It's an alphabetical list, so this 1st statement says, in effect, 'count everything from the start of the alphabet to the end of the letter c': PHP Code: ...........
View Replies !
Small Query Optimization
I need this query optimized using indexes. So I was wondering how I could optimize it to work with an index for speed.The query is: Quote: select ided from products where dates <= 2006-11-11 or cost=0.00 or url not like 'http://%' or imgurl not like 'http%' I tried making an index on (dates,cost,url,imgurl) but it doesn't seem to use the index when I do the explain part. I find that even when I miss of the like parts of the query it still is not using the index. It seems to not use the index because of the "or" that is used in the query instead of the "and". Could this be the reason and how could I over come this so that it will use the index.
View Replies !
Mysql Simple Query
I am doing a project in mysql and php.. While inserting the data, i made a mistake of inserting date values into the month field and viceversa. can i swap it with a query. I cant take a backup of this table.. because its very big and the webspace for mysql database for me is running very low. ONE MORE THING :- there are many tables almost 565... can this all be swapped in a single query or procedure.
View Replies !
Simple Mysql Query
I know this will be simple but I just cant get it to work and I am being tested here by my line manager. Our database is named: lsql_Category and we have a table column named: Keywords We have thousands of rows The column Keywords has data in most of the rows and some without any data at all. Normally assorted and various text words. I want to clear all data from this coloumn but DO NOT want to delete the coloumn cos we want to input data at later time. The result should be that the column Keywords remains but will not have any data. Can someone help? I think it is a select command and a where command but that is all I know.
View Replies !
|
TRACKER
