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

  1. should I also do vertical partioning and divide the table into 2 tables ?
  2. 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 ?
| improve this question | | | | |
  • 2
    "will have 100000 entries per day to store" and "planning to have MyIsam engine" (which means it is a new project, not a legacy issue) is a bad idea. If you value your data, please consider using a transactional engine. I still use MyISAM nowadays for new projects, but only for pure caching/derived, smallish results that can be lost at any time. See: dbahire.com/… – jynus Apr 2 '16 at 17:24
  • 1
    Agree that innodb would be a better engine for that scale. – RLF Apr 2 '16 at 18:13
  • I dont need FK constraint and also I will insert the records in the night by a cron job and need only read operations throughout the day, so; how innodb is preffered over MyIsam? @jynus – Jatin Seth Apr 3 '16 at 3:50
  • 1
    One single write fails-- or the database crashes during writes, and you will lose data. If lucky, only the parts that are being written. If unlucky, most of the table. I work with MySQL a lot and I have seen that happening many times. Not a single time with InnoDB (that was not related to hardware/fs failures). The larger the table, the more common that will be- there is a non-written limit for myisam tables. There are reasons to use MyISAM, but only if that data is not canonical (the reference copy is somewhere else). You will also hit concurrency problems sooner due to MyISAM's table locks. – jynus Apr 13 '16 at 18:33
  • Also, if you insert in concurrency, you may have even better performance. – jynus Apr 13 '16 at 18:35

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.

| improve this answer | | | | |
  • thanks, that make sense to me, will size of other tables also affect the permormance of this table ? – Jatin Seth Apr 2 '16 at 17:38
  • If you are just using the 'vertical partition' the other tables shouldn't add much weight to your query. If you do need to use data from other tables then you need to assess what data is needed and whether that table also needs to be partitioned. But I would not think it a general necessity except if I/O is a serious constraint. – RLF Apr 2 '16 at 18:09

Horizontal PARTITIONing, without a clear picture of why, is usually a mistake. There are very few cases where such is beneficial to performance. Please describe your PARTITION BY... clause and why you think it will help.

I agree that InnoDB is the way to go today.

Vertical partitioning is sometimes useful. Again, need more details to give a straight answer. Let's see SHOW CREATE TABLE. If the 38 columns are full of TEXT and big VARCHARs, then vertical partitioning is probably a good idea for MyISAM, but less useful for InnoDB, since the latter engine may put such columns in another place anyway. If the 4 columns are wider than the 38, then this argument does not hold much weight.

You are looking at 40M rows per year? Will you be deleting "old" records? If so horizontal partitioning is an excellent use case. Set up weekly or monthly or yearly partitions (but strive for 5-50 partitions) and use DROP PARTITION and REORGANIZE PARTITION. Details in my blog.

| improve this answer | | | | |
  • 1. Yes, the 38 columns are having text and varchar values; but why it is not useful in case of innodb ? 2. Yes, I am looking to save 40M or more per year 3. I will not delete the old records just let it be as we need only last six months records to show – Jatin Seth Apr 3 '16 at 3:08
  • I recommend you flip a coin, implement what it says to implement. Then, in a couple of months, re-think the decision. That is, I don't see any strong rationale for either method -- yet. – Rick James Apr 3 '16 at 5:23
  • Thanks for sharing your valuable knowledge; I am planning to do horizontal partioning by date of six months and will delete records older than 1 year also going to convert it into innodb for better performane. – Jatin Seth Apr 3 '16 at 17:41

Many optimizations in database administration share a common goal:

  • Reduce the working set of the data so that less needs to be kept in memory

Since the granularity of caching is done at a page level (both in InnoDB and de-facto in MyISAM due to filesystem block), having large 42-column rows means that you will fit fewer rows per page on average and there is no split where the hot sub-set of columns are kept in memory while the inactive ones can not.

This results in a sort-of cache dilution, where you may require more memory than if you were to normalize the schema and split into a few different tables.

(Note: InnoDB does overflow large text/varchar/blob columns to separate pages. I agree with Rick's comment that InnoDB is the way to go.)

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.