Sql-server – dm_db_missing_index_details – does the order of equality_columns matter

The direct answer for this would be

information_schema.statistics

mysql> desc information_schema.statistics;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512)  | NO   |     |         |       |
| TABLE_SCHEMA  | varchar(64)   | NO   |     |         |       |
| TABLE_NAME    | varchar(64)   | NO   |     |         |       |
| NON_UNIQUE    | bigint(1)     | NO   |     | 0       |       |
| INDEX_SCHEMA  | varchar(64)   | NO   |     |         |       |
| INDEX_NAME    | varchar(64)   | NO   |     |         |       |
| SEQ_IN_INDEX  | bigint(2)     | NO   |     | 0       |       |
| COLUMN_NAME   | varchar(64)   | NO   |     |         |       |
| COLLATION     | varchar(1)    | YES  |     | NULL    |       |
| CARDINALITY   | bigint(21)    | YES  |     | NULL    |       |
| SUB_PART      | bigint(3)     | YES  |     | NULL    |       |
| PACKED        | varchar(10)   | YES  |     | NULL    |       |
| NULLABLE      | varchar(3)    | NO   |     |         |       |
| INDEX_TYPE    | varchar(16)   | NO   |     |         |       |
| COMMENT       | varchar(16)   | YES  |     | NULL    |       |
| INDEX_COMMENT | varchar(1024) | NO   |     |         |       |
+---------------+---------------+------+-----+---------+-------+
16 rows in set (0.01 sec)

You could SELECT from that table with

SELECT * FROM information_schema.statistics
WHERE table_schema='mydb' AND table_name='mytable';

or see the statistics by doing

SHOW INDEXES FROM mydb.mytable;

Please keep in mind that this table is not always accurate in a write-heavy environment. Periodically you will have to run ANALYZE TABLE against all MyISAM tables that are updated frequently. Otherwise, the MySQL Query Optimizer, which relies on information_schema.statistics, can sometimes make bad choices when developing EXPLAIN plans for queries. Index statistics must be as up-to-date as possible.

ANALYZE TABLE has ABSOLUTELY NO EFFECT against InnoDB tables. All index statistics for InnoDB are computed on demand by means of dives into the BTREE pages. Therefore, when you run SHOW INDEXES FROM against an InnoDB table, the cardinalities displayed are always approximations.

UPDATE 2011-06-21 12:17 EDT

For clarification of ANALYZE TABLE, let me rephrase. Running ANALYZE TABLE on InnoDB tables is completely useless. Even if you ran ANALYZE TABLE on an InnoDB table, the InnoDB storage engine performs dives into the index for cardinality approximations over and over again, thus trashing the statistics you just compiled. In fact, Percona performed some tests on ANALYZE TABLE and came to that conclusion as well.

Depending on the RDBMS storage engine, it may reorder the fields internally for storage and display anyway. If we look at this question from the angle of a C program, a table in a database is like a C struct. E.g. A table in a c program might look like:

typedef struct _Table {
  char type,      /* 1 byte   */
  int id,         /* 4 bytes  */
  char *name;     /* 4 bytes  */
  char city[25];  /* 25 bytes */ 
} Table;          /* 34 bytes total */

You can see that in your program, you will expect that your first struct element is at the first pointer location, then one byte over will be your second element location, etc... These are called offsets.

Your RDBMS will likely store the file data structure in a c style struct so that it can understand what the offsets (memory locations) of data will be for each row. Then indexes will be applied to make searching amongst like groups of rows. An index location is simply a pointer to the first byte of each matching row (or struct).

As a software engineer type, you'd probably want to pack the struct so that the smallest data types are at the beginning of the struct so that as your pointer arithmetic scans over them you'll find conclusions faster, however this is a design decision.

Bottom line, best practice says to design your model smallest to largest, but it's probably being re-organized by the storage engine requirements anyway.