1

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?

Improve this question
0

2 Answers 2

Reset to default
5

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

Improve this answer
11
  • 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.
    – user1822
    Commented Sep 15, 2012 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)
    – Stephen Senkomago Musoke
    Commented Sep 15, 2012 at 14:11
  • @a_horse_with_no_name I have removed the statement specifying its for varchar columns only, thanks
    – Stephen Senkomago Musoke
    Commented Sep 15, 2012 at 14:15
  • @a_horse_with_no_name: There are limitations on indices though, that depend on the size of the char columns.
    – ypercubeᵀᴹ
    Commented Sep 15, 2012 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)
    – user1822
    Commented Sep 15, 2012 at 14:36
-1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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