Mysql - How number of columns effect the database performance

I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal partioning for the table but my questions are

• should I also do vertical partioning and divide the table into 2 tables ?

• If we select only 4 columns from a table in query then does the presense of other columns affect the query even if those are not in column list ?

If you have 42 columns to store and you are selecting only 4 columns, then yes the other columns can affect the query since it increases the amount of disk I/O the server uses to select the wider table.

If you primarily need the 4 columns, doing a vertical partition would give a smaller I/O overhead and could result in better performance for that query.

Likewise, a covering index for the 4 columns could also be processed with less I/O than the wider 42 column select.

Will it be faster? It should be, but it all depends on what else is involved in the process but not explicitly mentioned in your question.

StackExchange Database Administrators Q#134171, answer score: 5