… 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… be slow as it is quite fragmented. Table however is not getting any table scans just single row lookups by primary key. Also… rather than before it in the index ? They are not selective plus we might want to get all thumbnails for given…
Post: To UUID or not to UUID ?
March 13, 2007 By 28 Comments
… an article comparing UUID and auto_increment primary keys, basically advertising to use UUID instead of primary keys. I wanted to clarify this a… again applies to Innodb tables aspect of primary key selection – you often can gain a lot by selecting primary key which provides data clustering which matches… 200 rows/sec and the it is still slowing down a bit as key file growths. So in this little case we…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… primary key is handled. I can’t see the index size difference between DEFAULT and 1 values for such key… benchmarks. I’m testing 4 types of queries: select count(*) from t1, t1 t2 where t1.c=…keys Be careful with PACK_KEYS=1 as it can slow down your integer joins a lot Be careful with PACK_KEYS…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… do Join not on primary key and have query to …select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row *************************** id: 1 select…check if using longer strings slows down things significantly and…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
… NULL, `block_id` int(10) unsigned default NULL, PRIMARY KEY (`tracker_id`,`username`,`time_id`), KEY `block_id` (`block_id`) ) ENGINE=InnoDB Table… query that scans data by primary key was slow. How slow ? Let me show. The query in question is (Q1): SELECT count(distinct username) FROM…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…than data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated runs …where *************************** 2. row *************************** id: 1 select_type: SIMPLE table: dim_date type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ssb.lineorder.LO…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——-+——+————-+ | 1 | SIMPLE | test | const | PRIMARY,k | PRIMARY | 4 | const….01 sec) The difference between these cases is “i” is primary key while “k” is simply indexes column. Note: In some cases… up in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…PRIMARY KEY (`l_orderkey`,`l_linenumber`), KEY `i_l_shipdate` (`l_shipDATE`), KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`), KEY…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… you will need to have your queries do something like SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The… range scans on what would be primary key but it also can slow down your inserts and make primary key significantly fragmented. I also would… cases should auto-increment be used as primary key? A: Auto-increment is a good default primary key. You should pick something else if…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…make InnoDB use a composite UNIQUE key on a table as a primary key taking up far more space…545,654,34) > instead of: > SELECT * FROM articles WHERE user_id IN (SELECT id FROM users WHERE featured=…your queries, especially > if you see them in slow query log. If you have trouble understanding > …
