Re: Using 'Order By' disables use of index
Harrison Fisk wrote:
> It isn't always true in MySQL with ALTER TABLE.
> The reason is that the "cluster" isn't maintained
> automatically. So it can't be used by the
> optimizer to make decisions as it has no
> information if it is still roughly in order. It
> will still filesort, but it will be faster to do
> so.
>
I have a question about this, about using "ALTER TABLE" to do sorting on disk. What if the table is large, is written (mainly INSERTs) about 1,000 times a day but accessed for SELECT about 100,000 times a day?
It is important to have the primary key index because many of the SELECTs depend on it, but there is a "modify_date" in this table too, and many users want their data sorted by date (which is also the default in our interface).
We already have about five composite indexes on the table, which are not covering indexes, just composite ones based on what we read about left prefix concept in the MYSQL documentation. Having an ALTER table in this scenario would make sense, if it conflicts with the ordering of the primary key?
A side question: for indexing, does it matter:
1. If an indexed column is CHAR(1) or tinyint? Is one faster than the other?
2. Or, if the indexed column is mediumint versus varchar(20)?
I would imagine that indexing shouldnt make that much of a difference based on column type?
Thanks,
CM