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.

I have a database for my catalog webpage. Some tables are about 3GB with millions of rows.

What engine should I choose for my database, InnoDB or MyISAM?

There will only be about 1,000 new records a week, and about 1,000 deletes, but I will modify some data in one table quite often (price, quantity, etc.). All other tables will be "sealed".

I need indexes to enable fast queries. Which storage engine is best for this scenario?

share|improve this question

2 Answers 2

My recommendations are to start as follows:

  1. Go for the latest version of the MySQL Server that you can get, there have been some pretty impressive work done on 5.6 although the production ready version is 5.5

  2. Go for InnoDB - comes as the default for the higher MySQL versions

  3. Configure InnoDB as follows:

    • file_per_table - creates an data file for each table instead of putting all tables in a single data files
    • innodb_buffer_pool_size - 80% of available RAM (if a dedicated machine) so that as much of your database is in memory
  4. Table column characteristics:

    • use the smallest column size needed for the data
    • use numeric foreign keys
    • index the columns used in the searches, and setup the indexes to cover the most common searches, for example if you search on columns 4, then 6 alot create a multi-column index with column 4 first followed by 6

Remember these are just starting points you can fine tune specifics as you move along, but before you fine tune, measure tune then measure again

share|improve this answer
    
a varchar(50) column with two characters does not take more space than a varchar(500) with two characters. So I don't understand the "use the smalles column size needed" comment. –  a_horse_with_no_name Sep 15 '12 at 14:02
1  
For a fixed number of characters say US state use CHAR(2), if you have a column with a range of numbers, using an unsigned TINYINT for 0 - 255 is better than BIGINT or plain INT, if you only need a date use DATE instead of DATETIME, VARCHAR(255) is smaller than VARCHAR(256) and above which are taken as blobs (If I remember correctly) –  ssmusoke Sep 15 '12 at 14:11
    
@a_horse_with_no_name I have removed the statement specifying its for varchar columns only, thanks –  ssmusoke Sep 15 '12 at 14:15
    
@a_horse_with_no_name: There are limitations on indices though, that depend on the size of the char columns. –  ypercube Sep 15 '12 at 14:33
    
@ypercube: does that mean you cannot create indices on "large" varchar/char columns? (Not that they usually make sense though for really large values) –  a_horse_with_no_name Sep 15 '12 at 14:36

pretty sure that's a no since that would be a pretty expensive op if you have a ton of rows and the optimizer is trying to make things fast..so the fastest info it has would come from the table definition. That might have changed post 5.1 FYI

share|improve this answer

Your Answer

 
discard

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

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