Hi,
I'm working on a database program and I noticed that when I'm doing queries with tables that have TEXT/BLOB columns, MySQL will use temp tables and use filesort. I'm not entirely sure when and how this happens, but are there cases where MySQL does not use filesort when there are BLOB/TEXT columns? Like if the sort column is type of VARCHAR() with an index?
If the sort has to be done on the varchar and if the text or blob column has to be included in the results, then MySQL will do a filesort even if you have an index on that varchar. A filesort can be avoided only if all the columns you are retreiving are present in the covering index. In this case, there is no optimal way of adding the text column to the index.
One of the best ways is to horizontally split the table into two and the second table just containing the text/blob columns. This way, the sorting can be done on the first table using the index and then a join will be performed with the second table.