… a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed… more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This… table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time…
Comment: Derived Tables and Views Performance
May 21, 2013 By 16 Comments
…KEY (`id`)) —————————————————————————————————— $mysql=mysql_connect(’127.0.0.1′,’root’,”); mysql_select_db(‘test’,$mysql); function display_menu($parent, $level) { $result = mysql_query(“SELECT…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
…key constraint could lead to unresolved replication conflict and leave a slave hanging. Bug fixed #1130888 (Seppo Jaakola). If MySQL… failure due to insufficient privileges. Fixed by excluding lost+found folder if found. …D Prabhu). In case CREATE TABLE AS SELECT statement was running in parallel with…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… So this workload produces SEQUENTIAL inserts into Primary Key, that this is quite suitable for InnoDB,… is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`…._repair_threads = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed inside single-statement transactions… read-only transaction optimization in MySQL 5.6, because all SELECT queries in the AUTOCOMMIT mode are, by definition, read-only transactions, so… obvious. In the following test sysbench does 9 primary key SELECTs followed by a PK UPDATE statement in each thread in a loop…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
… or NOT NULL can shift all values by a byte and thus will spoil the result…mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS | COL_NAME | +———-+—–+———-+ | 1679 | 0 | actor_id | +———-+—–+———-+ Which means the primary key…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…only 20 partitions; they are PARTITION BY RANGE (to_days(…)). However, the ranges … anywhere in any ‘free’ variant of MySQL. PARTITIONs are scanned one at a … would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar … based on a UNIQUE/PRIMARY key and it can prune, the delete …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN_USAGE contains information about both primary keys and foreign keys. If the REFERENCED_* columns are non-null, it’s a foreign key. mysql> SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…query in particular that had this surprising outcome: mysql> SELECT * FROM cast_info WHERE role_id = 1 and… id: 1 select_type: SIMPLE table: cast_info type: ref possible_keys: role_id_note key: role_id_note key_len:… just 266 (5.6), reducing the executing time by almost 5 times. Pro tip: make sure you…
Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
… default ’0′, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 mysql> select i,count(*) cnt from trunc group by i order by cnt desc limit…” here Lets look at query execution: | Handler_read_next | 1305742982 | mysql> select count(*) from trunc where i=4147483647; +———-+ | count(*) | +———-+ | 0 | +———-+ 1 row…
