…MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX…Key redundant ends with a prefix of the clustered index # Key definitions: # KEY `redundant` (`col2`,`id`) # PRIMARY KEY…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
… see which tables are referenced the most, and which are candidates for dropping…mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY… based on names, tables without indexes, columns flagged ‘nullable’ and ‘must be unique…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…. Q: Doesn’t the primary key solution for random selection only work when the IDs for movies are distributed uniformly over the range… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the… table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… details are below. Here is a brief context recap. There are two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Post: Finally. How to verify if all MySQL records were recovered
April 25, 2013 By Leave a Comment
… case the same question arises: How many MySQL records were recovered and how many were lost… an InnoDB page are organized in an unidirectional list. The list is sorted by primary key and starts …page: ./constraints_parser -5f pages-actor/FIL_PAGE_INDEX/0-1599/00000000-00000003.page -V … Checking …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… of locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically on a single… no effect on standard MySQL replication from this instance, since MySQL replication is asynchronous. What about semi-sync MySQL replication? It’s actually… tradeoffs. Most of us are used to the tradeoffs we take when deploying conventional stand-alone MySQL Innodb with asynchronous slaves. We…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS | COL_NAME | +———-+—–+———-+ | 1679 | 0 | actor_id | +———-+—–+———-+ Which means the primary key…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…) DEFAULT ” NOT NULL, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I…, `k` int(10) unsigned NOT NULL DEFAULT ’0′, PRIMARY KEY (`hid`,`mid`,`id`) ) and transactions are: $HID=rand(0,10000) // distributed by uniform… = 1 myisam_recover socket=/var/lib/mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are all defaults, as I understand…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves …’ delete would mostly follow above two points. * Secondary indexes are also BTrees, so they need updating (although …://mysql.rjweb.org/doc.php/partitionmaint The third use case for PARTITIONing involves 2D-like indexing…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…are generally other considerations to take into account. He also didn’t answer the ‘Insert’ part. 1. With a standard numeric key id, set as a Primary Key, it will …be adding to a smaller index, and a smaller ‘chunk’ …on a partitioned table, as Mysql can fire off one …
