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 Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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?
URGENT: Please Help Me With My ON DUPLICATE KEY UPDATE Query
Could you please help me do this quickly. I have a query: Quote: mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=a") What I need it to do is that whne title and brand matches, it is then a duplicate listing. I have set up the unique key for this. When a duplicate listing is found it will then change the groupname value from "1" and update it with "a". When it then finds the next duplicate listing that has the same title and brand as the duplicate listing that had it's groupname changes to "a", I need it to be change to "b" this time. So I now have two listings that have a groupname of "a" and "b". Now when there is more duplicate listings that have the same title and brand as "a" and "b", I now need the groupname to be updated to the actual listings id and not use "a", "b", "c", etc... How can I do that. I know how I can do the first one so that it updates to 8, but then how would I do the 2nd and 3rd ones. Please help, I have to do this urgent as my database is really slow and it is damaging my site as it is taking about a minute or so to do each query. Also, I would prefer it if it could be done in one single query. I can also use variables in the query and if statements plus other php like Quote: mysql_query("INSERT INTO products (id, title, brand) SELECT id, title, brand FROM prodse WHERE approved=1 ON DUPLICATE KEY UPDATE groupname=$groupname")
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.
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?
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.
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 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....
Update If Duplicate
does anyone know how to do an update on a table only if there's duplicate entry in this table using a query?
ON DUPLICATE KEY UPDATE
i have query to set a login time, or update the time if a user is already on the database. $query = mysql_query("INSERT INTO logged (username,time) VALUES ('$userid','$time') ON DUPLICATE KEY UPDATE time='$time'") or die(mysql_error()); and the erorr i get back is You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE time='1110251002'' at line 1 im running MySQL 4.0.23-standard.
On Duplicate Update
I have tried to use the INSERT...ON DUPLICATE KEY UPDATE in Coldfusion. After I run the query the database seems to have the correct entries in it but Im getting an error message: Error Executing Database Query. [Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.11-nt]Duplicate entry '0-thisURL' for key 2 <cfqueryparam value="#now()#" cfsqltype="cf_sql_date">) v> Am I way off the mark here? Like I say the entries in the database seem sorrect but I still get the error.
DUPLICATE KEY UPDATE
Like many tables, mine has a PK with AUTO_INCREMENT set. I would like to roll my UPDATE and INSERT sprocs together into one to keep things clean. I'm having a hard time getting my head around how this 'ON DUPLICATE KEY UPDATE' works without checking for a null PK or -1 or something like that. In other words, if I'm inserting new data, I don't have the PK value and my table has it's PK column as AUTO_INCREMENT, so how do I handle that? Pass it NULL for the PK on INSERT and a valid PK on UPDATES? In the past I have done this with an IF ELSE block to check for existence, but the MySql docs lead me to believe this is built in somehow.
On Duplicate Key Update
The table has an autoincrement field that is the primary field. It also has id, date, and course_number that are indexed fields. Since the ON DUPLICATE KEY UPDATE how will I Insert and on duplicate update? The ON DUPLICATE KEY UPDATE works only when the field is the primary field. Will it work if the field(s) are indexed?
Update If Duplicate Found
Im currently running a statement that inserts a new row into the table if a duplicate row is not found: PHP $db->Execute("INSERT INTO tee_time ( SELECT $locationid AS locationid, (SELECT courseid FROM course_conversion WHERE coursename = '$currcourse' LIMIT 1) AS courseid, '$currcourse' AS coursename, '$currtime' AS teetime, '$currdate' AS teedate, '$currcost' AS cost, NOW() AS grabdate FROM tee_time WHERE coursename = '$currcourse' AND teetime = '$currtime' AND teedate = '$currdate' HAVING COUNT(*) = 0)"); The problem is if a row is found I need it to update that row instead of just skipping it. On duplicate wouldnt work because teetime and teedate can be the same in multiple rows.
Insert On Duplicate Key Update
Is it possible so do something like this "insert * from TempTabel on dublicate key update 'all fields'; " So I don't have to write every column name. I need this, so I can use the same code on multible tabels. I could use replace, but then the foreign key in other tables are not updated.
ON DUPLICATE KEY UPDATE Syntax
I have two tables with identical columns. I want to update records in tbl1 with record information from tbl2.Code: INSERT INTO tbl1 SELECT * FROM tbl2 ON DUPLICATE KEY UPDATE SET tbl1.col=tbl2.col, tbl1.col2=tbl2.col2 I seem to be missing something here...
Insert On Duplicate Key Update Question?
I have UNIQUE comp keys with 3 columns. I want to insert new rows if the data doesn't match one of the 3 columns. If they match, don't do anything. I am trying to do it with one query without doing select first and bunch of comparsion then either insert or update depending on the comparsion. I am think of using this: INSERT INTO mytable ('col1', 'col2', 'col3') VALUES ('mydata1', 'mydata2', 'mydata3') ON DUPLICATE KEY UPDATE col1=col1, col2=col2, col3=col3 is it going to work? I think it first tries to insert my data as a new rows if it is not duplicated. If duplicated, it will update the data with its old data. But update will ignore the operation since it's the same data.
Using ORDER BY With UPDATE To Resolve The Duplicate Key
I found this page, which is relevent to my problem: http://www.mysql.com/news-and-events/newsletter/2003-09/a0000000224.html I followed the suggestion and altered my query to this: UPDATE library_categories SET lft = lft + 2 WHERE lft > 1 ORDER BY lft DESC and I get this error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ' 1 + 1, + 2)' at line 1 Is there a way to get this query to work?
MySql UPDATE With Duplicate Rows
I'm trying to run an update on a linking table, the update is running into a Primary Key constraint violation, and in my workaround I've got stuck trying to write a DELETE statement. Here's the table I'm working on: Code:
LOAD DATA INFILE Or ON DUPLICATE KEY UPDATE
I want to use LOAD DATA INFILE to load a text file into my database because of the speed at which it can complete the import. But I don't think I can use it so I want to check here first before I completely nix the idea of using it. Is it possible, using LOAD DATA INFILE and possibly the REPLACE argument, to retain values from specific columns in the row that it's updating. It says in the manual that "You cannot refer to values from the current row and use them in the new row" but goes on to say something about using SET that I don't understand. Is there another way to use LOAD DATA INFILE and to retain values from specific columns that aren't being updated by a field in the text file? (i hope some of that makes a little sense?) If it's impossible to use LOAD DATA INFILE, how can I speed up my ON DUPLICATE KEY UPDATE query. Right now, it just processes 1 row of the pipe delimited text file at a time:
Update Values On Inserting Duplicate Index
I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index and is "unique". when I run insert ignore into `data` ( `field1`,`field2`) values (1,2) (2,6) (2,9) (5,1) I got 1,2 2,6 5,1 because the third value pair, (2,9) was ignored due to duplication in field1. here is what I want to do: when inserting new record with duplicate index, I want the existing record gets updated. in the above case, I want the output look like the following after the insert command: 1,2 2,9 5,1 after searching mysql online document, I only found the "on duplicate update" clause can do something similar, however, all the examples only show setting the duplicate record to somthing unrelated to the new values.
ON DUPLICATE KEY UPDATE - Don't Have A Key But Want To Update
This may seem like more of a PHP question but I will come onto the MySQL part in a minute. Basically I have a PHP script that lets users upload 2 different CSV files to different tables. The problem I have is that sometimes these CSV files are updated and then need to be reimported into the database through the script. I have made this script totally generic so that when you change the table that is being entered to the rest of the script will alter itself to import the correct CSV to that table. But a problem arises when I have a CSV file that contains each product sold and the shopper who bought the product (so the shopperID may appear in more than 1 row) - however this file does not have a unique id for each row meaning that when I use an INSERT INTO with a ON DUPLICATE KEY UPDATE... it will just insert more rows instead of updating rows because there is now key in the table. The format of the table is like this (with some example data to show you more clearly): +-----------+-------------+---------+------+--------+------+ | ShopperID | Product Code | Product | Price | Amount | Units | +-----------+-------------+---------+------+--------+------+ 546733 1315 prod 1 64.00 64.00 0 546733 1316 prod 2 43.99 43.99 0 OK just ignore the last 2 fields - that is a problem with the old system there was which does not add the units up right. Anyway basically I might have the same shopper ordering 2 things which will come up on different rows - I have 3 things I cannot do: 1. I can't make the ShopperID field the PRIMARY KEY because I have many times where there are 2 rows with the same shopper 2. And I cannot just do an UPDATE when it gets to a duplicate entry because it will overwrite every time it gets to a shopper that already exists. 3. I don't want to add a unique primary key to the CSV as this will mean that the system I am creating loses its ability of being automatic because the person (an admin) that uploads the file will have to make sure that it has that extra field which is auto incrementing. Does anyone have any ideas on how I can get round this? Here is my PHP function which contains the query: PHP // this function for insert data to csv function makeINSERTS($text, $table, $tablefields, $correctcsv){ $insert = array(); //make array for hold data insert $i = 0; $success = true; while(list($key, $val) = each($text)){ // Insert the data $insert[$i] = "INSERT INTO ".$table." VALUES('"; $insert[$i] .= implode("','", $val); $insert[$i] .= "') ON DUPLICATE KEY UPDATE "; foreach($tablefields as $k=>$field){ $insert[$i] .= "`$field`='{$val[$k]}',"; } $insert[$i] = substr($insert[$i],0,strlen($insert[$i])-1); // echo $insert[$i].'<p></p>' $result = mysql_query($insert[$i]); if(!$result) { echo 'FAILURE to insert/update any or all of the database form uploaded CSV!<br />' $success = false; die('Query failed: ' . mysql_error().'<br />'); } $i++; } if($success == true && $correctcsv == true){ echo 'SUCCESS in uploading the CSV file to the database!<br />' } else { } return $insert; }
Update INT UNIQUE Column - Duplicate Entry Error
As an example, I'm creating a table which holds following values in MySql 5.0: idcol lft (unique) 1 1 2 2 3 3 4 4 -> when I try to update the lft column using : UPDATE unique_example set lft = lft +1 where lft > 1; I get following error message : Duplicate entry '3' for key 2 Exactly the same approach works using SQL server 2000. Apparently, MySQL processes the rows one by one, as it throws an error on the UNIQUE lft column from whom I wish to update all rows at once? Code:
Query Help To Remove Duplicate Data
I have a query that returns: first name, last name, comments. I would like the result to be something like: Dan | Hudson | "here is a comment" | | "here is another comment" Lisa | Hudson | "my comment" but currently the name is repeated every time there is a comment. Is there a way to do a group by clause or something that will remove the duplicates? Dan
Query That Duplicate Row And Increment Date
I have a table EVENT id date duration 1 2006-01-01 3 2 2006-04-23 5 3 2006-10-01 0 is possible made a query that do it? id date 1 2006-01-01 1 2006-01-02 1 2006-01-03 1 2006-01-04 2 2006-04-23 2 2006-04-24 2 2006-04-25 2 2006-04-26 2 2006-04-27 2 2006-04-28 3 2006-10-01
Eliminating Duplicate Results From Query
I'm trying to dynamically build pull down menus based on the contents of a column (in order to build a search query). But the column may have the same data entered multiple times. This results in a pull down that has the same option listed several times. I wonder if I can eliminate duplicates from that array? So that it only appears once in the array.
Duplicate Yet Diferent Data In Sql Query
Is there a single SQL Query that will do the following? mysql> SELECT * FROM user; +----+----------+ | id | username | +----+----------+ | 1 | Bill | | 2 | Bob | | 3 | Hank | | 4 | Jim | +----+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM email; +----+-------+------+---------------------------------------+ | id | _from | _to | message | +----+-------+------+---------------------------------------+ | 1 | 1 | 2 | Blah, blah, blah | | 2 | 1 | 3 | I told Bob to Blah, Blah, Blah | | 3 | 3 | 4 | Bill told Bob to Blah, Blah, Blah | | 4 | 4 | 2 | Did Bill tell you to Blah, Blah, Blah | +----+-------+------+---------------------------------------+ 4 rows in set (0.00 sec) What SQL Query would result in the following...??? mysql> SELECT ??????????????????????????????; +----+-------+------+---------------------------------------+ | id | _from | _to | message | +----+-------+------+---------------------------------------+ | 1 | Bill | Bob | Blah, blah, blah | | 2 | Bill | Hank | I told Bob to Blah, Blah, Blah | | 3 | Hank | Jim | Bill told Bob to Blah, Blah, Blah | | 4 | Jim | Bob | Did Bill tell you to Blah, Blah, Blah | +----+-------+------+---------------------------------------+ 4 rows in set (0.00 sec)
Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is: The key of one of the record has the format: Rssss The key of the second record has the format: ssss How can I delete both records from the table?
Ignore Duplicate Query Items
This formcode retrieves faxes from a DB but some are the same and I do not want those in my list... how do I tell it to ignore duplicates?PHP Code: $sql = "select c.fax as fax ". "from #__comprofiler as c ". "left join #__users as u on c.user_id = u.id ". " where (c.user_id = u.id) and (u.block =0) and (c.fax != '') and (u.usertype != 'Super Administrator') and (u.name != 'guest') ". " order by c.fax";
Help With Updating Duplicate Rows In Mysql Query.
Quote: SELECT id, count(*) AS numlist FROM products GROUP BY category, name, brands HAVING numlist > 1 ORDER BY id ASC What it does is find the duplicates. I also have a column called "app" which has a default set to "1". So now what I want to do is that when duplicates are found, I want the first duplicate row in each group to stay as a "1" and the others in the same group to be updated to a "2". I need this done for every group. How can I do this. I have looked high and low accrossed the web, but can't seem to find any solution. I have managed to find out how to group them and count the number of listings in each group, but I can't seem to figure out how to do the rest. Also, if possible, I would like it to be done with one query.
Complex Query - UPDATE Within UPDATE?
Edit: Before anyone leaves this thread, don't be put off by the regular expressions! They are not the problem, so please stay and read. OK, this query has got my head spinning. I am basically creating a query that goes through each product in a table to update the stock for that particular item with that particular size (i.e. I am talking about shoes - different models and each model has different sizes (uk kids 12 -> uk 11). With each shoe it does (or is meant to do) the following: 1. The PHP script that runs the query is looping through every size outside of the query 2. So for each of these sizes it checks to see whether the product it is currently on matches the size it is on 3. When it finds the size it is on, it then deducts the correct number of units from the stock table 4. The final WHERE clause makes sure this subquery inside the UPDATE only happens when the StockUpdated field of the Product table equals 0 (in other words, the stock hasn't been counted before) Basically what I need to do, is first to make sure what I currently have got does the above correctly but also I need the query to UPDATE the StockUpdated field to 1 only when it has been updated successfully. How could I do this? Unfortunately I cannot just add an extra update entry to the end of the query as this would update the StockUpdated field regardless of whether it has been properly counted or not. Here is the query I have so far (with a little simple PHP around it doing the loop): PHP $shoesizes = array(1 => 'ukk12','ukk13','uk1','uk2','uk3','uk4','uk5','uk6','uk7','uk8','uk9','uk10','uk11'); $numshoesizes = count($shoesizes); for($i = 1; $i < $numshoesizes; $i++) { $stockupdate = " UPDATE heelys_stock,items_ordered SET heelys_stock.size_".$shoesizes[$i]." = (SELECT CASE WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]12( )?(' -- if UK Kids 12 THEN heelys_stock.size_ukk12 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^0-9]13( )?(' -- if UK Kids 13 THEN heelys_stock.size_ukk13 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]1( )?(' -- if UK 1 THEN heelys_stock.size_uk1 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]2( )?(' -- if UK 2 THEN heelys_stock.size_uk2 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]3( )?(' -- if UK 3 THEN heelys_stock.size_uk3 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]4( )?(' -- if UK 4 THEN heelys_stock.size_uk4 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]5( )?(' -- if UK 5 THEN heelys_stock.size_uk5 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]6( )?(' -- if UK 6 THEN heelys_stock.size_uk6 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]7( )?(' -- if UK 7 THEN heelys_stock.size_uk7 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]8( )?(' -- if UK 8 THEN heelys_stock.size_uk8 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]9( )?(' -- if UK 9 THEN heelys_stock.size_uk9 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]10( )?(' -- if UK 10 THEN heelys_stock.size_uk10 - (items_ordered.Amount/items_ordered.Price) WHEN SUBSTRING_INDEX(items_ordered.Product,',',-1) REGEXP '( )?(UK)?( )?[^0-9]11( )?(' -- if UK 11 THEN heelys_stock.size_uk11 - (items_ordered.Amount/items_ordered.Price) FROM items_ordered WHERE items_ordered.StockUpdated = 0) WHERE (heelys_stock.id = (SELECT heelys_stock.id FROM heelys_stock,heelys_shoe WHERE SUBSTRING_INDEX(items_ordered.Product,',',1) = heelys_shoe.full_shoe_name)) , items_ordered.StockUpdated = 1" // at the moment this last update of the items_ordered table happens to every record!!! even if the other part of query fails // update stock for size $i mysql_query($stockupdate); } Hope someone can see how I can do this? I've been working on this query for 2 or 3 hours now and I've been making reasonable progress but now I am really stumped.
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.
|
TRACKER
