…PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `lineitem_fk2` (`l_suppkey`), KEY `lineitem_fk3` (`l_partkey`,`l_suppkey`), KEY `li_shp_dt_idx` (`l_shipdate`), KEY…to know what is ‘Using Index condition’, what I was able to find is Baron’s bug with no mention in…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…, PRIMARY KEY (ancestor, descendant) ) ENGINE=InnoDB; Because there isn’t much written about using the Closure Table design, I periodically get questions about how to… SELECT from the same table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to find any path that…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
…find it later, plus it is quite handy in a way it presents information: SELECT CONCAT(table_schema, ‘.’, table_name), CONCAT(ROUND(table…primary key with Innodb tables. Of course it also could be perfectly fine tables but it is worth to look. Changing the query a bit to…
Post: Percona XtraDB Cluster (PXC): what about GRA_*.log files ?
December 19, 2012 By Leave a Comment
… in ROW format representing the failed transaction this post explains how to proceed. The first step to be able to analyze your GRA files is to…_rows event on table sakila.actor; Duplicate entry ’447′ for key ‘PRIMARY‘, Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event…, aborting… In this case it’s obvious why it failed but it’s not always the case. Now you know how to find the…
Post: Working with large data sets in MySQL
July 5, 2007 By 12 Comments
…Table Locks or transactions). Besides these various trips and gotchas you simply need to plan carefully how you’re going to…find people miscount is assuming data management operations will be proportional to the database size. Say it takes 30 minutes to alter 10GB table…table to fit in… primary key…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…Very little tuning was done. The goal was to see how MySQL 5.6 performs out-of-the-box… full table scans access the table by primary key. This forces the storage engine to touch the same page numerous times in … a set of drill-down queries to find an anomaly. I am going to focus on the first query, …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…to match the order of your common queries and b) choose your primary key correctly. Let’s go back to the ad-by-day table…to think about how to avoid enormous tables that are hard to…to find a lot more bugs and annoyances. There are already inconvenient limitations on some key…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…and how to use this info to improve MySQL Performance. To …table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table, in…But in parent table `test/parent`, in index `PRIMARY`, the closest match we can find is…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
…in Innodb (by using its internal hidden primary key) but this functionality is not exported well as it requires you to get rid of primary keys…in table ‘t’” error message. If you find Falcon or Innodb behavior better still prepare for this to…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…to say about this? *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t type: ref possible_keys: PRIMARY key: PRIMARY key…find several bug reports regarding this problem (e.g. #8569, #19548). Some replies from MySQL indicate this may be eventually fixed in…
