…PRIMARY index of table sakila/actor still resides in InnoDB dictionary although marked as deleted. Knowing index_id, it’s easy to get the table…PRIMARY KEY (actor_id), -> KEY idx_actor_last_name (last_name) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; Query…
Post: Duplicate indexes and redundant indexes
August 17, 2006 By 38 Comments
…to create PRIMARY KEY and it will enforce unique values and will be used in the queries. The other case is simply having multiple keys…find them get rid of them. Note: Duplicate indexes apply to indexes of the same time. It may make sense to have indexes of different types to…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…to satisfy foreign key conditions (have column child table is referencing and have it of proper type and indexed): mysql> set foreign_key_checks=0; Query…
Post: MySQL 6.0 vs 5.1 in TPC-H queries
March 25, 2008 By 17 Comments
…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…to execute query with IGNORE KEY (li_shp_dt_idx) (full table…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 27 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query… long 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 look…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…table called ontime_stage. Then, the airport information was extracted: create table dim_airport( airport_id int auto_increment primary key, unique key…Query to vastly improve the performance of queries which have to examine a large amount of data. Shard-Query…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… to textbook solutions. CREATE TABLE TreePaths ( ancestor CHAR(1) NOT NULL, descendant CHAR(1) NOT NULL, length INT NOT NULL DEFAULT 0, PRIMARY KEY… 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 terminate at a descendant of D…
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
…to find them. But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query SELECT DISTINCT s.TABLE…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
…some non PRIMARY …of tables having few indexes – should you run multiple queries in parallel to get best preload speed ? It depends – depending on key/clustered key…queries at once to get more outstanding requests at the same time – benchmark to find out. If you just need to preload single large table…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…table Bar: CREATE TABLE Foo (A INT, B INT, PRIMARY KEY (A, B)); CREATE TABLE Bar (ID INT PRIMARY KEY, X INT, Y INT, FOREIGN KEY…find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN_USAGE contains information about both primary keys and foreign keys…
