…index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB: Database page corruption on…we found there is corrupted data in the table and we need to somehow skip over it. To do it we would need…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
…index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index…we change it: If we disable index on the second column we get a full table…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… single thread depending on system and table structure. Now if you data fully on disk (both data and index) you would need 2+ IOs to… 100 rows/sec rate. So we would go from 5 minutes to almost 4 days if we need to do the join. Some people assume join would be close to two full table scans (as 60mil of rows need to…
Post: Why you should ignore MySQL's key cache hit ratio
February 28, 2010 By 27 Comments
… time of our query. All we can do is guess, like good Pavlovian…really hard to measure, alas! So we need to either pick a surrogate, …on how important those tables are to you, how big the indexes are on disk, and so on…and authoritative, but still makes no sense, why wouldn’t you obsess over it…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…tables – naturally an index on the referencing columns in the child tables…need an index on order_date to help with finding orders for a given date. 8 columns 6 indexes on…why most if not all widely used databases do not require indexes on FKs. The database designer should be able to choose what to do…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
…index key parts as soon as there is a range clause on the previous key part. So if you have INDEX… group_id (and in this case you do not even need to trick around equity propagation to make… list table. So we finally managed to get better performance by joining data to yet another table though why it …
Post: Impossible - possible, moving InnoDB tables between servers
June 8, 2009 By 9 Comments
…needs in this: – when we need to restore only single table from backup (sometimes developers kill only single table, not whole database 
) – to copy single table…on root leafs of secondary indexes also in system tablespace. And that’s why we…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
…we need to get 5 names of cities, located in Minnesota and, to make things slower, we…why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states s ON…index (for the case when data fits in memory!) So, if you have an application and you need to have some table…
Post: Why Index could refuse to work ?
September 8, 2006 By 13 Comments
…in set (0.00 sec) Why on the earth index would not… force index (PRIMARY) where article_id=10; +—-+————-+———+——+—————+——+———+——+——-+————-+ | id | select_type | table | type …will fit strings if we need it to, but… (it can do index scan though). I …
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…we often do is extending index when there are queries which can use more key part. Typically this is safe operation, unless index…index covering query now – it does not need…index it will quite likely be unable to use new index to full extent. What is solution ? You can have “redundant” indexes on…
