We will be undergoing scheduled maintenance on May 20th, 2013 at 02:00 GMT.
MySQL 5 Administration-Part 1 Tutorials
Architecture / Query Cache
Subtitles of the Movie (MySQL 5 Administration-Part 1 / Architecture / Query Cache)
In this lesson we're going to take a closer look at what the query cache does. So we use a query cache to avoid performing the full execution of a Select Statement if we at all can. There are three things that the query cache contains for each statement that is being cached. The first thing is a hash of the query statement. So what it does is it has to use a hashing algorithm to generate this hash and that is then used in a hash index in the query cache. So basically think of the query cache as an index of sorts that uses this hashed value to look up a results set. The second thing then is the results set for that query and the third thing that is stored in the query cache is a list of the tables that were used in executing this query. In other words a list of tables that were needed in order to be able to bring back the results from that query. That's going to be important as we'll see in a bit. Now the query cache only works for exact queries in the cache. So let's say we issued a query twice and all we did was we changed one letter from an uppercase to a lowercase. When that gets hashed the hash values are going to be slightly different and therefore they're seen at least as far as the query cache is concerned as a separate query. We'll discuss that a little bit more when we get into actually tuning the query cache and so on but I, I just want you to know that it works in that manner. The query cache is best again for high read, low write tables. Tables that are relatively static because as soon as we make a change to the content of the table then all of the elements that are in the query cache, all of the cached queries that have anything to do with that table, need to be removed. Because we don't know whether that has ruined the results set for that query or not. Okay. Here's then what happens when the query cache is being addressed. When a query comes in and the query cache is turned on and all of those kind of things, the incoming query statement is then hashed. MySQL then uses that hashed value to look up the query in the cache. And if it's there that's great, we don't have to go through all the other process of executing that query. All we have to do is return the results set back again. But if the query does need to be performed, when the execution engine gets done with everything, gets it all packaged up and sends it back to the client, it also sticks it into the query cache. So that the next person that comes along and tries to perform that same query will be able to get the results back from the query cache rather than having to go through the process of generating it again from the database. Now if that table, if any table associated with that query is updated in some way, if we perform an insert, update or delete on that query, then MySQL considers all the queries that are associated with that table in some way to be stale queries. In other words the data could be bad. And so it simply removes those queries from the query cache. Again, we're going to learn about all of these things in some detail and actually watch it execute once we know enough and about all of the administrative features of MySQL so that we can deal with tuning this query cache. But I want you to at least know that this is one of the things that's coming up. Assuming that the query cache isn't being used and we had to go through the parsing the next step is going to be to work with the query optimizer. And that's going to be the subject of the next lesson.
Tutorial Information
| Course: | MySQL 5 Administration-Part 1 |
| Author: | David Swain |
| SKU: | 34307 |
| ISBN: | 978-1-61866-086-2 |
| Release Date: | 2012-12-31 |
| Duration: | 16 hrs / 171 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
