3 ms·

First, 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 EXPLA

by lysium 15y ago

First, 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.

nerfhammer 15y ago
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.
- mildweed 15y ago
  Could somebody please point me to a longer yet still intelligent comparison of MyISAM and InnoDB? Thx.
  - nerfhammer 15y ago
    Short version: MyISAM doesn't support transactions and only supports table-level locking, so a heavy concurrent read/write workload will bring it to its knees. Generally you shouldn't use MyISAM anymore unless you know what you're doing and have a specific reason for doing so. InnoDB is much more crash-safe, supports transactions, and can support concurrent read-writes using row-level locking. It's generally a much more advanced storage engine than MyISAM and it's what Oracle is investing its development efforts in going forward.
- chrisbolt 15y ago
  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?
  - nerfhammer 15y ago
    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...]

by lysium 15y ago

nerfhammer 15y ago

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.

mildweed 15y ago
Could somebody please point me to a longer yet still intelligent comparison of MyISAM and InnoDB? Thx.
- nerfhammer 15y ago
  Short version: MyISAM doesn't support transactions and only supports table-level locking, so a heavy concurrent read/write workload will bring it to its knees. Generally you shouldn't use MyISAM anymore unless you know what you're doing and have a specific reason for doing so. InnoDB is much more crash-safe, supports transactions, and can support concurrent read-writes using row-level locking. It's generally a much more advanced storage engine than MyISAM and it's what Oracle is investing its development efforts in going forward.
chrisbolt 15y ago
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?
- nerfhammer 15y ago
  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...]