… case you could be reading the same page multiple times into the buffer pool. So considering this …rows are read from table1, the values of indexed columns that will be used to perform a lookup… by PK column and then performing the lookup, and then there are other possibilities like InnoDB doing read…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… from the index – typically (unless it is covering index) MySQL gets a “row pointer” which can be primary key value (for Innodb tables… Index merge works much better than multiple column indexes. This is in case you’re using OR between the columns. In this case the combined index…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
… I mentioned a way I use to preload Clustered Index (data) for Innodb tables. Though I thought this topic would benefit from… tbl WHERE index_col like “%0%” for each index. Only one such query per index is enough even if it is multiple column index. To fetch BLOB/TEXT externally stored columns you can…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… Index creation will allow Innodb to build indexes by sorting rather by row by row insertions as it currently does which can be multiple… Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column. This starts…+ Heikki also mentioned there are more things inside Innodb to unleash performance such as multiple purge threads or parallel log recovery features…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
…index parts are actually used for row lookup. Very common problem I see is multi column indexes…index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
…. Q8: How do you assess current state of Innodb scalability with multiple threads and multiple concurrent transactions ? HT: The scalability in my opinion…) Question: How much space InnoDB allocates for each blob outside of the page? HT: For each column that InnoDB needs to store ‘externally… record. In that source code tree, InnoDB only needs to store locally a prefix of an indexed column. PZ: I think it is…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… faster. I disagree with you on this completely because InnoDB uses a clustered index based on (in order) the primary key you…-conctextual primary key (NCPK – an auto_increment column) in nearly (emphasize nearly) every InnoDB table can have very serious and negative performance… seen developers ask the database to do row lookups by multiple columns of varchars is likely. Don’t do it. I can…
Post: My Innodb Feature wishes
May 4, 2006 By 30 Comments
… page if many columns are long strings. I would like to see it at least doing it for indexed columns only or even better have option similar to PostgreSQL to specify where this column should be stored… well as with innodb_file_per_table=1 allowing to flush all of modified tablespaces in the parallel. Multiple page sizes. One…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…multiple of boolean values (yes/now or something similar) in the table and if you get many columns…Innodb which allocates 1 byte for each BIT(1) column. So we can get 1 byte per column for boolean flag storage in Innodb…: 23068672 Max_data_length: 3096224743817215 Index_length: 1024 Data_free: 0 …
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…’, mysql can use either indexes built on multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we should understand… record => >=10GB for data. Each index will be between 5GB and size of data (for innodb). May be I’m wrong here…
