Search Results for: create index on large table

Getting History of Table Sizes in MySQL

One data point which is very helpful but surprisingly few people have is the history of the table sizes. Projection of data growth is very important component for capacity planning and simply watching the growth of space used on partition is not very helpful. Now as MySQL 5.0+ has information schema collecting and keeping this […]

InnoDB TABLE/INDEX stats

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB: It is INNODB_TABLE_STATS INNODB_INDEX_STATS These table show statistics about InnoDB tables ( taken from InnoDB data dictionary). INNODB_TABLE_STATS is | table_name | table name in InnoDB internal style (‘database/table’) | | rows | estimated number of all rows […]

table_cache negative scalability

Couple of months ago there was a post by FreshBooks on getting great performance improvements by lowering table_cache variable. So I decided to investigate what is really happening here. The “common sense” approach to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL […]

Multi Column indexes vs Index Merge

The mistake I commonly see among MySQL users is how indexes are created. Quite commonly people just index individual columns as they are referenced in where clause thinking this is the optimal indexing strategy. For example if I would have something like AGE=18 AND STATE=’CA’ they would create 2 separate indexes on AGE and STATE […]