3

I am designing a table (InnoDB) that stores information regarding product items.

items ->

  • item_id (PK)
  • price
  • weight
  • stock_count
  • category_id
  • description (BLOB, max 2K)

This table is primarily read to fetch all data for a given item, though sometimes the description field alone can be read. There is infrequent update to an item's data, and that includes the description field.

I have 2 queries:

  • For faster read operation, is it better to segregate the table into 2 parts, 1 with fixed width fields and other with the blob 'description' field? I think the basic understanding I am missing is whether the variable length of the description field will impact the lookup time of the item data.

  • If I hold lots of items data, say 10M, how is the update speed of this table compared to the segregated table mentioned above.

Rob
  • 4,927
  • 12
  • 49
  • 54
Ethan
  • 4,915
  • 1
  • 28
  • 36
  • I'm not positive but I believe, if you're not using the indexes on a table the table scan's will take longer with the variable length descriptions. If however, the queries are using well defined indexes, It won't really matter. The index will point to the physical location on the drive – xQbert Feb 21 '13 at 19:54
  • From your question, it's not clear if you're also using the `FIXED` MyISAM table type. You should generally not use that if you have a `BLOB` field or any other variable length fields. – G-Nugget Feb 21 '13 at 19:55
  • @G-Nugget It's InnoDB. Updated the question. – Ethan Feb 21 '13 at 20:15
  • 1
    Instead of `stock_count` can you just perform the calculation of when items are received/sold. – Kermit Feb 21 '13 at 20:21
  • @AarolamaBluenk: Good tip. I can create another table where I update currently available stock in 1 column and another column gets incremented on a sale. – Ethan Feb 21 '13 at 20:28

1 Answers1

2

InnoDB gains no advantage from fixed-width rows. That's a MyISAM optimization.

In InnoDB, long BLOB/TEXT/VARCHAR columns may overflow onto additional pages of storage (and memory, since the buffer pool has the same page organization as the tablespace).

Short BLOB/TEXT/VARCHAR that fits within the row's primary page will just live there with the other columns. Even when they overflow, the default row format (COMPACT) still includes the first 768 bytes of such a big column.

For more details, you would probably like to read http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/

So if you're concerned about fitting more rows per page, you could split the table and store your Description column in another table.

When updating, it's even less important, because InnoDB writes to its log files at the time of the update, and the log files are not organized in pages like the tablespace is. Later, the modified pages in the buffer pool are written down to the tablespace but your application doesn't have to wait for that, it happens in the background.

It may not be as big a benefit as you think, though, and it's probably a better strategy to optimize by using indexes to narrow down the number of rows your query has to visit.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for the link. In future, probably my description field can go to 5K or beyond. Regarding the 16K off-page allocation mentioned in the link, is it then better to change the field from BLOB to VARBINARY? (I am storing zlib-compressed utf8 text). – Ethan Feb 21 '13 at 20:42
  • 1
    BLOB and VARBINARY are treated the same. The only difference is that you can declare a DEFAULT value for a VARBINARY column. A similar difference exists for TEXT vs. VARCHAR. – Bill Karwin Feb 21 '13 at 20:44
  • 1
    You might also like to consider InnoDB's ROW_FORMAT=COMPRESSED. See http://dev.mysql.com/doc/innodb/1.1/en/innodb-compression.html Don't bother if your data is already compressed in the application, but I mean you could use table compression as an alternative to app-based compression. – Bill Karwin Feb 21 '13 at 20:45
  • Thanks. Do you also want to have a look at my other question, http://stackoverflow.com/questions/15010467 and provide me some tips. – Ethan Feb 21 '13 at 21:01
  • In the 2nd last para, you touched log files in connection with table update. For updates to these table, I will not have binlog enabled. In that case, does MySQL do something to provide similar speed? – Ethan Feb 23 '13 at 15:21
  • 1
    The InnoDB redo log files (ib_logfile0, ib_logfile1) are different than the binary log. I know, MySQL calls too many things "logs" :-) Anyway, the binary log filtering doesn't affect the InnoDB redo logs. – Bill Karwin Feb 23 '13 at 17:32
  • Can you look into another query of mine here http://stackoverflow.com/questions/15178227. Thanks. – Ethan Mar 02 '13 at 19:06