… the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the IDs for… against multiple columns in a compound index, then the order would matter, and it would have to match the order of columns in… table, and making lookups to the `kind_types` table by primary key, the result was a query that took half the time…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… simply cannot replace in short term. Control Your Auto-Incrementing Columns PXC/Galera controls auto-incrementing values internally within the cluster… you are planning to write on multiple nodes. Have PRIMARY KEYS If you still have tables without PRIMARY KEYs, then its time to make one…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…locking in Innodb is a single row (well, the PRIMARY KEY index entry for that row). This means typically … does NOT mean you can’t modify data on multiple cluster nodes simultaneously. You can. It does NOT … Example 2, above, how above moving the ‘joined’ column to the users_groups table so we don’…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key…-clustered tables. The other benefit – it is typical to show multiple thumbnails from the same album/domain, and due to clustering… indexes are defined. The primary key itself does not get much larger whatever columns you place into it, as primary key BTREE contains all table…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
… covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb tables) physical file offset (for MyISAM tables…(40) DEFAULT NULL, KEY `i1` (`i1`), KEY `i2` (`i2`), KEY `combined` (`i1`,`i2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I made columns i1 and i2… merge works much better than multiple column indexes. This is in case you’re using OR between the columns. In this case the…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… do you assess current state of Innodb scalability with multiple threads and multiple concurrent transactions ? HT: The scalability in my opinion is… referenced columns of a foreign key constraint need not form a primary key or a unique constraint: it is sufficient if the referenced columns form…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
… experience that failing to use a non-conctextual primary key (NCPK – an auto_increment column) in nearly (emphasize nearly) every InnoDB table can… you don’t create either a unique key or a column with an auto_increment primary key in a table using InnoDB, the database… seen developers ask the database to do row lookups by multiple columns of varchars is likely. Don’t do it. I can…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
…, but if we stick to simple MySQL using multiple indexes on most selective columns would be good idea for performance of such… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——-+——+————-+ | 1 | SIMPLE | test | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE… difference between these cases is “i” is primary key while “k” is simply indexes column. Note: In some cases even if it is…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…_buffer_size, then multiple hash tables would be…PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `i_l_shipdate` (`l_shipDATE`), KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), KEY…key o_orderdate which results in random scans of the PK to fetch the columns that are not part of the secondary key…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
… intended, but INSERTing in them is a nightmare. I use multiple record INSERTs (150 each time), and the first INSERT usually…/ORDER CLAUSEs). I’ve chosen to set the PRIMARY KEY using the first 4 columns, because the set of the four has to… of the PRIMARY KEY?! Could it be faster if I’d just assigned a different [FOREIGNER] KEY for every capital letter column, and a…
