…engineer-years spent developing and testing the query optimizer. The developers have to…to be consecutive. Then you could choose a random value, look up the row with “=” instead of “>” and be guaranteed to find…tables. It seemed to prefer an index-scan of 7 rows in the `kind_type` table to…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…TABLE_ID`,`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 So, `TABLE_ID` is our table_id. `ID` here is the index identifier. We need it to find…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
…’s difficult or impossible to find the right threshold for …engine. For example, a pile-up of connections all trying to open or close tables… recently. It is surprising how often a server crashes,…alert on a queries-per-second threshold. (I don… include more plugins to monitor more types of problems, …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…table and we need to somehow skip over it. To do it we would need to find…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
…to scan half the page in average to find the key value So how…TABLE `t1` ( `id` int(10) unsigned NOT NULL , `c` varchar(20) NOT NULL default ”, KEY `c` (`c`), KEY `id` (`id`) ) ENGINE…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…engines…to compose the complex object which was previously normalized to several tables or perform complex queries finding…table etc. Prefer full table scans to index accesses – For large data sets full table scans are often faster than range scans and other types…
Post: How expensive is a WHERE clause in MySQL?
October 31, 2008 By 17 Comments
…to test for some time. How much overhead does a trivial WHERE clause add to a MySQL query? To find out, I set my InnoDB buffer pool to 256MB and created a table that’s large enough to test, but small enough to…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
…per_table=1 as the InnoDB system tablespace cannot be compressed, see this page for further details on how to enable compression for a table. To…TABLE statements. In our case, we chose to re-build the table using ALTER TABLE via a null-operation like this: ALTER TABLE `t1` ENGINE…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…search engine marketing, online advertising, user behavior analysis, and many similar types of … those events up into a per-day table. Next, let’s look at… still have to think about how to avoid enormous tables that are hard to maintain, … column record only blue clicks; to find out non-blue clicks you…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…goal was to see how MySQL …table scans access the table by primary key. This forces the storage engine to…to find an anomaly. I am going to…PER_THOUSAND_GETS: 0 NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 0 *NOT_YOUNG_MAKE_PER… *************************** id: 1 select_type: SIMPLE table: lineorder type: ref …
