… MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20… notice that the above query satisfies all conditions required for MySQL to use an index but it does not. I just… note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if…
Post: Connecting orphaned .ibd files
May 13, 2011 By 12 Comments
…` bigint(20) unsigned NOT NULL default ’0′, `N_COLS` int(10) default NULL, `TYPE` int(10) unsigned default NULL, `MIX_ID` bigint(20) unsigned…_INDEXES: CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) unsigned NOT NULL default ’0′, `ID` bigint(20) unsigned NOT NULL default ’0′, `NAME` varchar… the dictionary in ibdata1. # ./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/sakila/actor.ibd -d sakila -t actor actor…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… `ex1` ( `val` bigint(20) NOT NULL DEFAULT ’0′ ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +——-+—————————————————————————————————–+ 1 row in set (0.00 sec) mysql> select count…: CREATE TABLE `search_set` ( `val` bigint(20) DEFAULT NULL, `cnt` bigint(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; mysql> insert into search_set values…
Post: Checking the subset sum set problem with set processing
May 16, 2011 By Leave a Comment
… Shard-Query: CREATE TABLE `the list` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `val` bigint(20) NOT NULL DEFAULT ’0′, PRIMARY KEY (`id`), KEY… that I have a lot of numbers in my list: mysql> select val, count(*) from data group by val; +—–+———-+ | val | count… rows in set (20.47 sec) Now insert a value which will cause our check to pass: mysql> insert into data (val…
Post: MySQL Partitioning - can save you or kill you
December 11, 2010 By 14 Comments
I wanted for a while to write about using MySQL Partitioning for Performance Optimization and I just got a relevant …: CREATE TABLE `tbl` ( `id` bigint(20) unsigned AUTO_INCREMENT NOT NULL, `uu` varchar(255) DEFAULT NULL, `data` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY… reduced performance for random selects by UUID from 400 to 20 per second (from single thread). Decreasing number of partitions made…
Post: Talking MySQL to Sphinx
April 19, 2009 By 4 Comments
…ids in it, removing everything else: CREATE TABLE `sptest` ( `id` bigint(20) unsigned NOT NULL, `site_id` int(10) unsigned NOT …| 2735703 | 139448919 | 2735703 | +————+——–+———-+———–+———+ 10 rows in set (32.47 sec) MySQL mysql> select max(forum_id) as m,author_id as …
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 41 Comments
… index: mysql> show create table wp_comments\G *************************** 1. row *************************** Table: wp_comments Create Table: CREATE TABLE `wp_comments` ( `comment_ID` bigint(20) unsigned…_type` varchar(20) NOT NULL default ”, `comment_parent` bigint(20) NOT NULL default ’0′, `user_id` bigint(20) NOT NULL default ’0′, `rajid` bigint(20) unsigned default…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… has 260M lines in it; each row is just two bigints. The bigger file was about 20GB and had wider rows…’s the CREATE TABLE: CREATE TABLE load_test ( col1 bigint(20) NOT NULL, col2 bigint(20) default NULL, key(col1), key(col2) ) ENGINE=InnoDB… the other terminal: while [ -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin…
Comment: COUNT(*) for Innodb Tables
December 20, 2011 By 29 Comments
… from another table, not null : mysql> desc messages; +—————+————–+——+—–+———+—————-+ | Field | Type | Null | Key | Default | Extra | +—————+————–+——+—–+———+—————-+ | id | bigint(20) | NO | PRI | NULL | auto_increment…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…_author_id` int(10) unsigned NOT NULL, `from_message_id` bigint(20) unsigned NOT NULL, `message_published` timestamp NOT NULL default CURRENT… we run: xtrabackup –stats –tables=art.link* –datadir=/mnt/data/mysql/ which will show something like this: table: art/link_out104… the post – if you are badly missing some features in MySQL, InnoDB, InnoDB-plugin, XtraDB, XtraBackup – you know whom ask for…
