1

I have a counts table which contains columns similar to (around 14 count columns):

user_id
friends_count
photo_count
video_count
blogs_count

This table gets updated whenever the user adds a photo or a friend.

I am going with InnoDB Engine since it is better to have row locking, but in the meantime also there are good number of reads on this table. For every user when they see profile of other user or even the users side menu contains all these counts.

Which engine is best suited for most writes and most reads like this?

Matt Fenwick
  • 48,199
  • 22
  • 128
  • 192
Bujji
  • 1,717
  • 10
  • 41
  • 66
  • 2
    Can you precise the following: Do you **require** that clients read only up-to-date data from the table? Do you **require** that the table data matches the **exact** object counts at any given point in time (as opposed to "they'll eventually match after a while")? – Romain Apr 26 '12 at 11:14
  • Are you sure, you want to keep this data doubled? Usually, counting on an index is quite quick (at least in Oracle, not sure about MySQL). The overhead of writing so many times probably outweighs the reading time for `SELECT COUNT(*) FROM tbl WHERE indexed_field = index_value`... – Lukas Eder Apr 26 '12 at 11:14
  • have you tried reading http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html – Shirish11 Apr 26 '12 at 11:21
  • Thanks Romain for your response . Yes clients needs the Exact Object counts .There are other important counts other than the mentioned ones – Bujji Apr 26 '12 at 11:42
  • Thank You Lukas for your response . Yes the counts are required ,since I have to display all the counts to client at a time on the screen . The counts all from around 14 tables , So I can not display these counts dynamically by querying – Bujji Apr 26 '12 at 11:44
  • Thanks Shirish11 for your response . Yes I have read the things from there and also googled and not able to come to any conclusion – Bujji Apr 26 '12 at 11:45
  • @Bujji: You're probably right on your MySQL setup. I'm spoiled with Oracle, which has this awesome [query result cache](http://www.oracle-developer.net/display.php?id=503) since 11g. This would automatically cache the counts in the DB, and invalidate those caches as soon as any relevant table is modified... – Lukas Eder Apr 26 '12 at 11:46
  • Thanks Lukas for the information about Oracle . – Bujji Apr 26 '12 at 11:53

2 Answers2

3

I looked at the different articles and especially these three links

http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf

Should I use MyISAM or InnoDB Tables for my MySQL Database?

http://www.quora.com/Which-MySQL-Storage-engines-are-used-by-Facebook

and came to conclusion it is better to go ahead with InnoDB .

Any comments on this also appreciated

Thanks and Regards

Kiran

Community
  • 1
  • 1
Bujji
  • 1,717
  • 10
  • 41
  • 66
0

General purpose MySql/MariaDB Engines

  1. XtraDB is the best choice in the majority of cases. It is a performance-enhanced fork of InnoDB and is MariaDB's default engine until MariaDB 10.1.

  2. InnoDB is a good general transaction storage engine. It is the default MySQL storage engine, and default MariaDB 10.2 storage engine, but in earlier releases XtraDB is a performance enhanced fork of InnoDB, and is usually preferred.

  3. Aria, MariaDB's more modern improvement on MyISAM, has a small footprint and allows for easy copying between systems.

  4. MyISAM has a small footprint and allows for easy copying between systems. MyISAM is MySQL's oldest storage engine. There is usually little reason to use it except for legacy purposes. Aria is MariaDB's more modern improvement.

Source of info: MariaDB Engines

S34N
  • 7,469
  • 6
  • 34
  • 43