-1

I have question about low level principle about mysql.

Let me suppose that I have A table with 4columns and no index.

select * from A vs select first column from A.

Then is there difference on read I/O as performance. not network I/O.

I'm curious about principle that read data from disk.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
dongyoung heo
  • 73
  • 1
  • 4

1 Answers1

2

In MySQL's default storage engine, InnoDB, the whole row is stored together. So there's little difference between SELECT * and SELECT first_column. It still has to do the I/O to load the page that contains each row, and once it has done so, it has all the columns in the buffer pool, so there's no significant increase in cost to reading more columns.

There's an exception case: if you have long strings in VARCHAR, VARBINARY, TEXT, or BLOB columns, the part of the string that doesn't fit on the page with the row is stored on additional pages elsewhere in the database storage (see https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/). If you omit string columns you don't need, you can reduce I/O because InnoDB won't need to read the extra pages for the long string data.

The above assumes, as you stated in your question, that there are no indexes. If you do have indexes, you might benefit from the covering index effect: if you reference only the column(s) that are part of the index, InnoDB can avoid reading the page with the whole row. It will just read the page that contains the referenced index entries. That will help reduce I/O.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I didn't upvote this just because of the formatting. Make of that ambiguous statement what you will. – user207421 Aug 04 '17 at 03:04
  • Thank you very much. I understood some understanding. Then My real problem is that I have to make table with 10 columns and 3columns have long strings but not indexes. So is it more effective that make 3 types table than make 1 table. – dongyoung heo Aug 04 '17 at 05:30
  • @dongyoungheo, make 1 table, unless you have a problem you can measure. Don't try premature optimization. But also, avoid using `SELECT *`. – Bill Karwin Aug 04 '17 at 06:34