Nicholas, ----- Original Message ----- From: ""Nicholas Elliott"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, July 11, 2003 6:04 PM Subject: InnoDB Performance issues
> ------=_NextPart_000_003B_01C3479C.77A1AB60 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Hey all, > ... > create table basic_daily_grid( > date DATE NOT NULL PRIMARY KEY, > variable1 MEDIUMBLOB, > variable2 MEDIUMBLOB ... > variable9 MEDIUMBLOB > ); > ... > mysql> select date from basic_daily_grid_innodb; > ... > 317 rows in set (0.00 sec) > > mysql> explain select date, count(*) from basic_daily_grid_innodb group = > by date; > +-------------------------+-------+---------------+---------+---------+--= > ----+------+-------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra | > +-------------------------+-------+---------------+---------+---------+--= > ----+------+-------------+ > | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | = > Using index | > +-------------------------+-------+---------------+---------+---------+--= > ----+------+-------------+ > > > mysql> explain select date, count(*) from basic_daily_grid_innodb group = > by date; > ... > 317 rows in set (2 min 54.95 sec) > > > I assume this is due to versioning or some other transactional feature. = > Or, is this a bug, or am I doing something wrong? I don't quite see why = > grouping items that are all unique should be that much slower than not = > grouping. I need InnoDB for the unlimited table size, but I don't = > (Really) need transactions, commit/rollback, or checkpoints. it is a performance bug. I an ORDER BY MySQL may use more columns than are mentioned in the SELECT query, and InnoDB retrieves the whole row. If there is a big BLOB in the row, it can take quite a while. I may fix this to 4.1.1, but first I have to ask the MySQL developer if handler::extra(KEYREAD) is aware that in a clustered index all columns are in the index record. Workaround: put BLOBs to a separate table and use a surrogate key (= auto-inc column) to join it to a smaller table where the other columns are. > Any suggestions on solving this last hurdle? Its entirely likely I'll = > need to group by year and average the results, or something similar - = > and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm = > expecting too much? > > > Thanks, > Nick Elliott Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
