…multiple…index range lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns…results For the purpose of this benchmark, I have used TPC-H Query #3 and ran it on TPC-H dataset (InnoDB tables…
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…3096224743817215 Index_length…results of these tests using CHAR(0) is the most efficient if you would like optimal structure both for MyISAM and Innodb tables…+———-+ | 0 | +———-+ 1 row in set …
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
…multiple tables or if you have GROUP BY query the number of rows which need to be utilized to create the result set… to generate result set – for example any filter which can’t use indexes will make…indexes. If you have WHERE ID%100=0 you can have extra column divisible_by_hundred and keep it indexed…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…multiple indexes on most selective columns would be good idea for performance of such queries. For example you may put index…result set. In the example above we did order by by last column, in fact index can be used for…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
… row in set (0.01 sec) Notice the implicit conversion of c1 from text to mediumtext. This approach can result in modified… unsuitable for large databases because it requires multiple alter statements to be run on each table: 1) Drop FULLTEXT indexes 2) Convert target columns to…
Post: Using Flexviews - part one, introduction to materialized views
March 23, 2011 By 2 Comments
…result set is really a virtual table. It has columns and rows, just like a database table…table. When you execute a query on a view, the result set is ephemeral, generated at run-time for…result set are computed each time the view is accessed. If multiple…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
…column in this table has 10000 distinct value, so range 1..100 selects about 1% of the table. The times for full table…result sets. So if you’re dealing with large data sets and complex queries here are few tips Try to fit data set…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
…table scan is only one of replacement policy optimizations possible. Q8: How do you assess current state of Innodb scalability with multiple…for read ahead and results…index is required for fast lookups: it can be used if the columns are the first columns in an index…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… caching of result sets is…multiple tables gives some very important benefits – your data > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for…for rare occurrences of the “weird” setting of the column’s possibilities. Also, indexing the full width of very wide columns…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…indexes built on multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection, we should understand that mysql finds one set… give you twice smaller result set. However, you …table). 4(id)+1+1+1+1+1+1 = 10 bytes per record => >=10GB for data. Each index…
