…traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization…enough so that the index tuples fit in the buffer. But there is no performance improvement when the workload is in-memory, in fact MRR adds…
Post: MySQL performance on EC2/EBS versus RDS
August 4, 2011 By 10 Comments
…. Here are some observations you can consider. They don’t cover all cases, but they are things to consider. The Amazon… call the working set) to fit in the InnoDB buffer pool, which means you need it to fit in memory. The largest instances currently have… partitioning so all the inserts go into one partition, whose indexes fit in memory. The more elaborate you get with this — such as putting…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… the second one? That doesn’t make any sense! The index covers x,y and col_a and should be eliminating a… row in set (0.00 sec) Conclusion: My examples were only on a small amount of data (16 000 rows) that fitted in memory, but the original query would have full table scanned if I didn’t use a FORCE INDEX hint. Add…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… Buffers, Indexes and Joins. Buffers First thing you need to take into account is the fact – situation when data fits in memory and when… apples to index lookus and joins which we cover later. As everything usually slows down a lot once it does not fit in memory the good solution is to make sure your data fits in memory as good as possible. This…
Comment: Countless storage engines
May 4, 2007 By 6 Comments
… in very interesting position. For small data sets when data mostly fits in memory fact Falcon does not have clustering by primary key and covering indexes… optimized index retrieval does not really matter if data is in memory. So this leaves rather narrow range for working set to memory ratio…
Post: Join performance of MyISAM and Innodb
May 29, 2006 By 6 Comments
… small enough to fit in memory and so buffer pool. I tested very simple table, having with about 20.000 rows in it on…, integer indexed column and indexed char column. The query is also trivial – the point was to make sure it is not index covered query… to request next row matching index Note: This applies to CPU bound workload with all content fitting in memory. In other cases situation is very…
Post: COUNT(*) vs COUNT(col)
April 10, 2007 By 41 Comments
…=latin1 mysql> select count(*) from fact; +———-+ | count(*) | +———-+ | 7340032 | +———-+ 1 row in set (0.00 sec) mysql> select count(val) from fact; +————+ | count(val) | +————+ | 7216582 | +————+ 1 row in set (1.17 sec) mysql> select count(val2) from fact…
Post: Redundant index is not always bad
August 28, 2007 By 7 Comments
… columns In the benchmark for this query we have 10 queries per sec. Usuall technique we use for such queries is covering index, which store all needed columns in the index, and there is no need to read… available memory to fit only one index, that shows how can degrade performance if we balance on memory limits) If you are interested in, the…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most cases when query can… though was small enough to fit in the systems memory. I’ve benchmarked simple queries using where clause which covers multiple columns: Q1 SELECT… benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth…
Comment: Do you always need index on WHERE column ?
August 28, 2007 By 16 Comments
… matter the cardinality, index traversing speed depends on the index cardinality. Note in this case it is not pure index scan (“Using index” in explan) but… the rows in the table in non sequential order. The 3.5 difference we see here matches given case – when data fits in memory so… so count(name) should be same as count(*) and so covering index could be used while MySQL is not smart enough for…
