…. We will need four of them: SYS_TABLES SYS_INDEXES SYS_COLUMNS SYS_FIELDS SYS_TABLES Here InnoDB keeps correspondence between human…_parser -f /var/lib/mysql/ibdata1 3. Recover SYS_TABLES, SYS_INDEXES, SYS_COLUMNS and SYS_FIELDS from indexes 0-1, 0-3, 0… SET ‘utf8′ COLLATE ‘utf8_general_ci’ NOT NULL, `last_update` TIMESTAMP NOT NULL, PRIMARY KEY (`actor_id`) ) ENGINE=InnoDB; In the…
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 from… UNSIGNED)), 10, 16)), 0) AS crc FROM `foo`.`bar` FORCE INDEX (`PRIMARY`) WHERE (1=1) FOR UPDATE; When you update your…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… am constantly referring to the amazing MySQL manual, especially the option and variable reference table. But just…Com_show_charsetsblogpercona.commanual Com_show_collationsblogpercona.commanual Com_show_column_typesblogpercona.commanual Com_show_contributorsblogpercona.commanual Com_show_create_…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=…MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX… unix_timestamp/integer or a ctime; indeed, running SELECT MIN(UNIX_TIMESTAMP(update_…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…the columns from the INSERT part of the statement corresponding to the columns in the secondary index, and … host_id TINYINT UNSIGNED NOT NULL, last_modified TIMESTAMP NULL DEFAULT NULL, PRIMARY KEY(id), UNIQUE KEY(… be considered as being defined first. So, MySQL checks our INSERT, sees that the next…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… use indexed_col=N is good. function(indexed_col)=N is bad because MySQL Typically will be unable to use index on the column even… order by while second which uses direct column value needs to do the filesort: mysql> explain select * from tst where i=5… be possible: mysql> explain select * from tst where i=5 and date(d)=date(now()) order by unix_timestamp(date(d)) \G…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
… check what the tool has logged: mysql> select * from test.foreign_key_errors\G *************************** 1….no index in referenced table which would contain the columns as the first columns, …timestamp when the error happened and the text of the error. We can see here that it was caused by an index…
Post: Descending indexing and loose index scan
May 9, 2006 By 6 Comments
… – these are features MySQL should get for all storage engines at some point. Descending indexes – This is something MySQL does not have at… workaround by having something like “reverse_date” column and using it for sort. With MySQL 5.0 you even can use triggers… see “reverse_timestamp” field in Wikipedia table structure. Loose index scan – Number of years ago when I just started using MySQL I thought…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
… was facing a peculiar problem where the rows column in the EXPLAIN output of the query was… to generate index cardinality estimates which are then in turn used by MySQL to pick and choose indexes and…` ( `id` int(11) NOT NULL DEFAULT ’0′, `created` timestamp NOT NULL DEFAULT ’0000-00-00 00:00:00…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… is index on `tag_name` in `tags`, there is index on (`itm_prd_id`, `itm_order_timestamp`) in `items_ordered` and indexes on other columns… index To remind – our structure design is: `itm_prd_id` int(10) unsigned NOT NULL `itm_order_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP…__and__itm_order_timestamp key_len: 8 ref: NULL rows: 1306 Extra: Using where; Using index In this case MySQL does not print…
