… that traditional index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index record to make… in how counter is calculated inside MariaDB? Other Observations Sometimes both for MariaDB 5.5 and MySQL 5.6, the optimizer chooses…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
… base point to see how column-oriented-analytic engines are better here. However, take into account, that for MyISAM we need to choose proper indexes to execute queries effectively, and there is pain coming with indexes: – load of data is getting slower; – to design proper indexes is…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
…You also may choose to set buffer …step would be to decide How Much …to adaptive hash indexes, ability to buffer writes and number of other factors so you just want to make your OS to give a way to…to get them in the swap. Make sure however the swapping is not happening ie your VMSTAT “si/so” columns…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…choose your aggregation to match the order of your common queries and b) choose your primary key correctly. Let’s go back to… you still have to think about how to avoid enormous tables that are hard to maintain, back up,…a set of primary key columns. And not only will every index be a little narrower,…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
…all the dimension columns are indexed on every table, and there’s a separate index on every column in the WHERE …No one ever let it finish to see how long it would run. How do I know it will run… table, and do index lookups in the two dimension tables. MySQL doesn’t want to choose this join order, …
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
… can use index to filter records before fetching any rows. How much it will…(City, Gender, Age, Available). So mysql can choose between two which is more selective…columns, it’s up to you decide what to do. 1. http://www.mysqlperformanceblog.com/2006/08/10/using-union-to-implement-loose-index-scan-to…
Post: Lost innodb tables, xfs and binary grep
November 9, 2010 By 11 Comments
…, some facts about the system and how data was lost: MySQL had …data dictionary table records to index records. Instead I had to grep for specific …of the columns were fixed size – 2bytes each, so the thing we had to look… happen to have a lot of data to scan and you have to choose between matching …
Comment: When should you store serialized objects in the database?
January 22, 2010 By 42 Comments
… gets converted to columns. The problem with when you choose to convert, is that you can’t easily get statistics such as how often the…. @Robin – Great to see you’re in one of the “when is it a good idea” cases. @Angelo – That indexing technique is… that you had some application error which caused an unindexed column to store the word broccoli as ‘brocoli’ [sic]. For me that…
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…to generate index cardinality estimates which are then in turn used by MySQL to pick and choose indexes and hence is irrelevant to….1 and how it calculates the row estimates. This bug was tracked down to http://bugs…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…to specify compressed block size, this is the size to which you expect each page to compress. If you choose… compressed to for data and for various indexes. How many “fragmented” pages do we have in the index… column. This starts giving you problems if you have a lot of concurrent inserts happening to…
