…_CLUST_INDEX. It doesn’t matter how the primary index gets created it will have minimal `ID` among the indexes of the table. mysql> select * from SYS_INDEXES WHERE TABLE_ID… SET ‘utf8′ COLLATE ‘utf8_general_ci’ NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB; In the…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and variable…create_eventblogpercona.commanual Com_create_functionblogpercona.commanual Com_create_indexblogpercona.commanual Com_create_procedureblogpercona.commanual Com_create_serverblogpercona.commanual Com_create_tableblogpercona.commanual Com_create…
Post: Recovery after DROP & CREATE
August 22, 2012 By 1 Comment
… in the pages. So, table_id and index_id of the dropped table gets overwritten. mysql> CREATE TABLE actor ( -> actor_id SMALLINT UNSIGNED…_name VARCHAR(45) NOT NULL, -> last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (actor_id), -> KEY idx…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
… ENGINE=InnoDB DEFAULT CHARSET=latin1 SYS_INDEXES: CREATE TABLE `SYS_INDEXES` ( `TABLE_ID` bigint(20) …some MySQL server. 0. Create empty InnoDB tablespace. 1. Create the table: mysql>CREATE TABLE actor…, last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…NULL, `message_published` timestamp NOT NULL default CURRENT_TIMESTAMP, `kind` enum(‘link…mysql/ which will show something like this: table: art/link_out104, index:…creates indexes by sort, so page fill factor should be quite good. To check that, there is xtrabackup –stats for index domain_id created…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… last_updated timestamp not null default current_timestamp on update current_timestamp, …id) references items(operator_id) ); create index order_date_idx on orders…MySQL will create the following indexes: unique index on order_id index on item_type_id index on locale_id index on currency_id index…
Post: Timezone and pt-table-checksum
September 25, 2012 By 1 Comment
…default value of CURRENT_TIMESTAMP. From the manual, here is how MySQL handles timezone locality with timestamp fields: Values for TIMESTAMP columns are converted… `data`, `date_created` + 0, CONCAT(ISNULL(`data`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `foo`.`bar` FORCE INDEX (`PRIMARY`) WHERE (…
Comment: Four ways to optimize paginated displays
September 25, 2008 By 27 Comments
… timestamp limit 1,10; create index test on log(timestamp); If the optimizer is relatively bright, he won’t table scan, but will instead index… much faster this way. From what I can tell, the MYSQL optimizer doesn’t take the limit clause into account (or… query returns 100,000 rows order by timestamp limit 99990, 10 is MUCH slower than order by timestamp desc limit 0, 10
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX…to a unix_timestamp/integer or a ctime; indeed, running SELECT MIN(UNIX_TIMESTAMP(update_time)) ……. was even stranger. I created a table with a similar structure: CREATE TABLE `foo` ( `i` int…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…created key: type_created key_len: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql…
