List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:July 13 2003 8:40pm
Subject:Re: InnoDB Performance issues
View as plain text  
Nicholas,

----- Original Message ----- 
From: ""Nicholas Elliott"" <nelliott@stripped>
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/


Thread
InnoDB Performance issuesNicholas Elliott11 Jul
  • Re: InnoDB Performance issuesDan Nelson11 Jul
  • Re: InnoDB Performance issuesNicholas Elliott11 Jul
  • Re: InnoDB Performance issuesNils Valentin12 Jul
  • Re: InnoDB Performance issuesNils Valentin12 Jul
Re: InnoDB Performance issuesHeikki Tuuri13 Jul