…: Doesn’t the primary key solution for random selection only work when the IDs for movies …. But the result was that it created a temporary table to count the movies per production …MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in the EXPLAIN…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… or SHOW CREATE TABLE crashes MySQL There is yet another source of information about the table structure – InnoDB dictionary. Let’s review tables from the…=InnoDB DEFAULT CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
…MySQL bug 65481 explains that the HUP signal also flushes tables in addition to logs. Flushing tables can impact running queries. Disable MySQL…/var/mysql/slow_query.log { nocompress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…explained in the original post, but seems to cause much confusion. Even though the read-only transactions optimization in MySQL…mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…table that I used for the test: mysql [localhost] {msandbox} (foo2) > show create table test_estimate \G *************************** 1. row *************************** Table: test_estimate Create Table: CREATE TABLE…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…mysql> SELECT * FROM cast_info WHERE role_id = 1 and note like ‘%Jaime%’; On a table like this: CREATE TABLE…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
…CREATE TABLE `trunc` ( `i` int(11) NOT NULL default ’0′, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
… tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a bit. mysql> create table… affected (0.11 sec) mysql> create table j3 (c1 int); Query OK, 0 rows affected (0.10 sec) mysql> explain extended select j1.c1 from j1…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…Create Table: CREATE TABLE `dl` ( `myday` date NOT NULL, PRIMARY KEY (`myday`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…row *************************** revenue: 446268068091 1 row in set (22.54 sec) mysql> explain select straight_join sum(lo_extendedprice*lo_discount) as revenue ->… D_HolidayFl tinyint, D_WeekDayFl tinyint ); DROP TABLE IF EXISTS lineorder; CREATE TABLE IF NOT EXISTS lineorder ( LO_OrderKey bigint not…
