… the combined size of the secondary key tuples fetched, and N is the buffer size. In MySQL 5.6 the buffer size… case of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache is… Handler_read_key and Innodb_rows_read are incremented by ONE. However, when MRR is used then there are two separate calls…
Comment: Heikki Tuuri Innodb answers - Part I
October 30, 2007 By 19 Comments
… hot cache. Read more here: http://dev.mysql.com/doc/refman/5.0/en/midpoint-insertion.html A second protection: multiple key caches. A table may be assigned to a separate keycache, whatever you’ll do with it it will never affect cached pages…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…range scans. We surely should benchmark this aspect separately. Improved Auto Increment handling Yet another known Innodb…in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible… stripes are better and why battery backed up cache is so important. I also usually speak …
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
…two separate queries: mysql> SELECT SQL_NO_CACHE * FROM count…mysql> explain SELECT SQL_NO_CACHE count(*) FROM count_test WHERE b = 666; +—-+————-+————+——+—————+——+———+——-+——+————-+ | id | select_type | table | type | possible_keys | key | key…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql…| Waiting for query cache lock | 0.000002 …keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…, so > database connection may become the issue. Caching should be > good help in this case though….t reference data in two separate databases in the same instance of MySQL. If nothing else, it … deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…when a battery-backed disk controller write-back cache does the same thing. PZ: First Indeed…_buffer_pool_size Q36: There have been several MySQL bugs opened about multi-core scalability (concurrent queries… the primary key? I suggest that you test reorganizing that table on a separate computer, maybe…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key… writes. Number of log/io operations allows to separate tablespace related IO from log related IO so…_commit=2 log writes are done to OS cache, and being sequential writes these logs writes are…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… make load parallel but still small enough to be well cached. It is also worth to note Innodb (which is storage… completely separate problem which can be fixed in MySQL 5.1 Data Clustering This again applies to Innodb tables aspect of primary key… process took over 12 hours and is still going. From MySQL status I can see it is loading about 200 rows…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… | id | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra … fit in cache well) When you want to run this query mysql first will…We do not really need separate subselect to make MySQL check WHERE clause while scanning…
