I'm confused; in the example, couldn't he avoid the filesort entirely by just adding an key on t2.t1id? Is it really optimal to be sorting the whole table -- no matter how optimally -- on any common serving path?
MySQL Text vs. Varchar Performance
21–23 of 23 posts
Re: MySQL Text vs. Varchar Performance
#22First, he mentions that VARCHARs are stored in-row, and then he wonders why his server's performance goes down when he joins on a VARCHAR(30000)? And uses EXPLAIN as the very last step? I don't quite get it, honestly.
He doesn't seem to be aware that there's a difference between storage engines when he says that "mysql" stores varchars in-row, when that's really MyISAM. MyISAM is now considered obsolete and you shouldn't be using it anyway. Innodb treats large varchars exactly like text/blob columns.
In the article he mentions that MySQL still uses MyISAM for temporary tables, which were being created by the query he was running (which, granted, did SELECT * which is usually a bad idea). If the size of the temporary table was the cause of slowness, wouldn't it affect InnoDB and MyISAM tables equally?
Re: MySQL Text vs. Varchar Performance
#23Earlier quoted context omitted.
He doesn't seem to be aware that there's a difference between storage engines when he says that "mysql" stores varchars in-row, when that's really MyISAM. MyISAM is now considered obsolete and you shouldn't be using it anyway. Innodb treats large varchars exactly like text/blob columns.
In the article he mentions that MySQL still uses MyISAM for temporary tables, which were being created by the query he was running (which, granted, did SELECT * which is usually a bad idea). If the size of the temporary table was the cause of slowness, wouldn't it affect InnoDB and MyISAM tables equally?
It would seem so.
It would probably always have been creating on-disk temporary tables because text columns aren't permitted in memory tables. It was probably just that the fixed-length row format forced them to become extremely large.
The performance advantage that he expected from the schema change would only arguably make sense with respect to whatever storage engine he was using, and it's actually not really true for InnoDB. [Actually come to think of it I can't think of any advantage do doing it for MyISAM either, I don't think it has a concept of overflow pages. InnoDB 1.1 has a lot of performance enhancements for text/blob columns, though...]