… ， I’ve tried your example “the affect gap locks to SELECT … FOR UPDATE ” on my server ,and my results are as… affected (0.00 sec) root@localhost:test 14:35:29>select * from t; +——+ | i | +——+ | 21 | | 30 | | 26 | +——+ 3 rows in set… intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… means if you have workload which includes some very long SELECT queries you can be potentially waiting for hours for this… values (“a”,1) | 0 | 0 | 1 | | 10304 | root | localhost | dumptest | Field List | 15 | Waiting for table flush | | 0 | 0 | 4 | | 10305… WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but resolving server gridlock…
Comment: Finding out largest tables on MySQL Server
March 30, 2012 By 24 Comments
Quick question. I have a large MYISAM TABLE with 4 fields ( id = primary key, categID = int index, subcategID = int index, content= blob [ this field has a serialized array - avg 9KB ] ) ; this table has 5…. The size of it is 9GB ( has serialized arrays ). An select like SELECT `content` FROM `table` WHERE `categID` = ‘categID’ AND `subcategID` = ‘subcategID…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
… application we can see a huge tables with varchar/char fields, with small sets of possible values. These are “state”, “… 1439943 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: s type: eq_ref possible_keys: …
Comment: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
… know how many pages the results set will span?: { 1. SELECT SQL_CALC_FOUND_ROWS id FROM table ORDER BY foo… primary key value) 3. SELECT fields FROM table WHERE id IN (collectedIds) 4. re-arrange ids 5.SELECT FOUND_ROWS(); 1 usually requires…. SELECT fields FROM table WHERE id IN (collectedIds) 4. re-arrange ids 5. If we originally got size + 1 rows back from select…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… compared to what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE as… gap waiting Record lock, heap no 180 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex… localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has a lot of lock structs…
Comment: Four ways to optimize paginated displays
September 24, 2008 By 26 Comments
… ( usually, a single column, the ‘id’ ). Once you do (example SELECT id FROM sometable ORDER BY someConditionThatCanBeSatisfiedByTheIndexData LIMIT offset, size), you… of the order they are coming out the table and SELECT fields FROM sometable WHERE id IN (idsFetchedEarlier) and then simply re…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
…, I don’t dump the whole rows, but only some selected fields that I need in the log). The trigger uses INSERT…
Comment: A common problem when optimizing COUNT()
September 25, 2008 By 18 Comments
… the battle. With the popularity of people doing stuff like: SELECT $fields FROM $table $join $where $order_by figuring out context is…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… chunk is 483762.46 (the query Q0 is: select avg(c1) from (select year,month,count(*) as c1 from ontime group by…` varchar(10) DEFAULT NULL ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; Last fields starting with “Div*” are not really used. Load procedure: Infobright… t2 ON (t.YEARD=t2.YEARD) InfoBright: SELECT t.YEARD, c1/c2 FROM (select YEARD,count(*)*1000 as c1 from ontime WHERE…
