Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.

My project's aim is to let users search a (huge) database of variety of products.

  • Each product is present under a category.
  • Each product will have 10 to 100 'specs' or 'features' by which the users will search.

The most common usecase is:

  1. User clicks on a category; then clicks various sub-categories if required.
  2. User starts off with 1 or 2 criteria and searches for products.
  3. User then keeps adding more criteria to the search to narrow down on the product.

I have three main tables 'products', 'features_enum' and 'features'. It is very important to let the data-entry users, create new 'features' on the fly, for the products - hence I am using EAV (anti)pattern.

Here are the structures of the tables:

'products'
    ID(PK), TITLE, CATEGORY
    (Indexed by CATEGORY)
'features_enum'
    ID(PK), TITLE
'features'
    P_ID, F_ID, VAL
    (Indexed by P_ID and then F_ID)

A sample format of my main search query:

SELECT
  p.ID,
  p.TITLE PROD_TITLE,
  fe.TITLE FEATURE_TITLE,
  f.VAL
FROM
  products p, features f, features_enum fe
WHERE
  p.CATEGORY = 57 AND
  p.ID = f.P_ID AND
  f.F_ID = fe.ID AND
  (
    (f.F_ID  = 1 AND f.VAL = 'Val1') AND
    (f.F_ID  = 2 AND f.VAL = 'Val2') AND
    ...
    (f.F_ID  = N AND f.VAL = 'ValN') AND
  )

My Experimentation So Far:

Due to my limited knowledge and experience in DBs, I hit a wall with theoretical planning. So, I generated a large set of test data to simply see what will work. All three tables had 500,000 test rows. Here are the avg. run times of the main search query:

  1. InnoDB without indexing: 90s.
  2. InnoDB with indexing: 15s. 0.3s after buffer pool size increase
  3. MyISAM without indexing: 9s.
  4. MyISAM with indexing: 0.7s.
  5. MyISAM with indexing + FIXED row type: 0.16s.

Test machine - Pentium 4 1.9GHz, 1.5GB RAM, IDE HDD, Win7.

I have basically not done anything to optimize other than indexing. So there might be a ton of things that I missed, that could have made InnoDB run faster. InnoDB buffer pool size was set to 16M (!!); I incremented it to 128M. Now, InnoDB is really fast. So one big reason for me leaning toward MyISAM is now gone. Maybe there is more that I can do.

Some points and long term usage estimates about the project:

  • 20 new products added daily, at roughly 20 x 100'specs' = 2000 record writes per day.

  • 1,000,000 page visits, and in worst case - same amount of search query runs per day.

  • Total record count for the tables is expected to reach 5,000,000 each.

  • Writes will be made by a semi-controlled group of people, where as read is public.

  • There are no complex 'transaction' type writes. The most complex write I can think of right now is - [one product row + 100 feature rows max] - at one shot

  • Require only a couple of constraints, but if necessitated by choice of MyISAM, I can enforce them at the application level itself.

  • DB access from other parts of the app - user registration, authentication etc.. will be few and far in between, I don't think they will have much effect.

Given all of that, I am biased towards MyISAM. But I need input from people already experienced in MySQL.

Questions:

  1. If the InnoDB run times are wrong/surprising, what have I missed in testing? Increasing buffer pool size dramatically increased performance. See above.
  2. If not, considering all of the above, is MyISAM really good choice in the long run?
  3. If MyISAM too turns out to be a bad choice later, how easily can I restructure the database? What options do I have?

On a side note:

  1. If choosing EAV was bad, what other architecture can I use for this project?
share|improve this question
    
This is a very detailed question, so thakns for that. What version of mysql? –  Derek Downey Sep 26 '12 at 15:19
    
@DTest - I ran the tests on v5.1.41, but for production I still haven't decided on the version or bought the server. –  user12940 Sep 26 '12 at 16:04
add comment

migrated from stackoverflow.com Sep 26 '12 at 12:23

This question came from our site for professional and enthusiast programmers.

2 Answers

In my experience with MySql MyIsam is really really fast on inserts and reads. On the other hand if you have many users hitting the database at the same time inserting and querying data - you will start to see the performance of MyIsam fall sharply.

MyIsam is good when:

  • you have alot of writes and very few reads
  • You have alot of reads and very few writes
  • You have a blog. Most of the time only a few writes a day between posts and comments

MyIsam is bad when:

  • You have alot of reads and writes happening at the same time

I have tested postgresql and it out performs mysql MyIsam when the database is getting hit hard with both reads and writes happening at the same time. Furthermore, MyIsam tends to get corrupted at least once a month when you have a high traffic website.

share|improve this answer
    
"MyIsam tends to get corrupted at least once a month...high traffic website", uh, that's scary! Does "traffic" here mean only the majority reads for my site, or does it also include my comparitively low 2000 writes? –  user12940 Sep 27 '12 at 6:54
    
Its easy to fix a MyIsam corrupted table. When It gets corrupted just do a repair; Done. 2000 writes is a lot if your myisam database is getting hit hard. It will slow down other connections trying to do a write. –  Luke101 Sep 27 '12 at 21:35
    
Hmmm... Then I guess MyISAM is bad for my application after all; and since I've sorted out the InnoDB performance issue, I will be using it. Thanks! –  user12940 Sep 30 '12 at 15:23
add comment

InnoDB and MyISAM each have their strengths and weaknesses.

If you have enough RAM, I would choose InnoDB because it caches data and index pages in the Buffer Pool. MyISAM only caches index pages in the Key Cache.

MyISAM tables experience full table locks for each INSERT, UPDATE, and DELETE. MyISAM tables always require disk access for data.

InnoDB tables always incur disk I/O in the following areas:

  • Double Write Buffer : Changes are posted in ibdata1 to avoid OS caching
  • Insert Buffer : Changes to Secondary (non-Unique) Indexes as posted in ibdata1
  • Data and Indexes
    • With innodb_file_per_table = 0, changes are written to ibdata1
    • With innodb_file_per_table = 1, changes are written to .ibd tablespace file. Read I/O against ibdata1 still necessary to crosscheck table metadata

SUMMARY

In an environment with the following:

  • heavy writes
  • heavy reads
  • tons of RAM
  • heavy connections

I would always choose InnoDB. Please check out my other post about InnoDB over MyISAM : When to switch from MyISAM to InnoDB?

When would I every choose MyISAM?

Under the following scenario

  • Using MySQL Replication
  • Master with all InnoDB
  • Slave with all tables converted to MyISAM
  • ALTER TABLE ... ROW_FORMAT=Fixed for all tables on the Slave

Disk I/O wise, MyISAM has a slight edge with ROW_FORMAT-Fixed because you only interact with one file, the .MYD file. The row size is completely predictable because VARCHAR is treated as CHAR this shortening access time for data retrieval.

On the other hand, InnoDB has to interact with multiple files (ibdata1, serveral read/write threads upon the .ibd of the InnoDB table).

share|improve this answer
    
Query run time of InnoDB was a PITA, but I've sorted it out after setting an appropriate buffer pool size. So, I've chosen InnoDB for the long run. Thanks for all the information! –  user12940 Sep 30 '12 at 15:20
    
Such a fantastic answer. Going to modify some of my code and test a new setup thanks to your answer - hopefully this allows me to not have to ask a similar question in regards to heavy reads + writes. Thank you so much Rolando. –  AndrewPK Oct 7 '12 at 19:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.