… for which this tool was originally built is long over, but still the tool serves well …, `price` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) )…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… the primary key values, and then instead of point primary key lookups, a range lookup is performed on the primary key by using the sorted primary key values…: SELECT key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer… dataset size ~95G), because the query was taking far too long to execute, ~11 hours in case of MySQL 5.5…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… random primary key BTREE updates which can be very expensive. Today I would like to share practical example when you may use long primary key…, as primary key BTREE contains all table data in leafs anyway. Top level pages can get a bit larger for long primary keys but usually it is not major. Non Sequential primary key This just comes as an effect of choosing url as the key…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
…. Clustering. I love Innodb’s ability to cluster data by primary key it is so helpful in many cases, but in other… inconvenient. Having independent CLUSTER=key option would be great, it also would help to avoid penalty of very long primary key – you can simply select not to cluster by it instead of creating fake primary key and promoting real…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… unlike other benchmarks I decided to do Join not on primary key and have query to read data for both tables. If…: 1 select_type: SIMPLE table: t1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 262144 Extra: *************************** 2. row… may suffer significantly if key compression is not disabled Joining on Shorter CHAR keys is significantly faster than Long keys Latin1 (I guess any…
Post: Predicting how long data load would take
May 24, 2007 By 12 Comments
… is well known it is important to load data in primary key order into Innodb tables for optimal performance, but effect does… bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool… we loaded data in parallel and our rows were relatively long. In most cases I would expect load speed to be…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… but for MyISAM can also help you to size your key buffer compared to operating system cache. I also use it… simply unused indexes among them) or may be there is long primary key with Innodb tables. Of course it also could be perfectly…
Comment: Duplicate indexes and redundant indexes
August 13, 2008 By 38 Comments
[...] to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may be there is long primary key with Innodb tables. Of [...]
Comment: Duplicate indexes and redundant indexes
February 4, 2008 By 38 Comments
[...] to data size often indicates there is a lot of indexes (so it is well possible there are some duplicates, redundant or simply unused indexes among them) or may be there is long primary key with Innodb tables. Of [...]
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… time, you should define the primary key as (day, ad). Don’t use an auto-increment primary key, and don’t put ad… levels of aggregations. Watch Out For The Long Tail People talk about the long tail and how you can focus on optimizing… assuming that there are indexes other than the primary key, we can shrink the primary key‘s width: create table ads_by_day_by…
