MYSQL Optimizing a table with 137000 rows

Question

I'm trying to optimize a redmine database before it gets too much of a pain; the Changes (basically a log of all the SVN Changes) is at 137000 rows (ish) and the table is set to the b asic default settings. No key packing etc.

The table is as follows

ID int[11] Auto Inc (PK)
changeset_id int[11]
action varchar[1]
path varchar[255]
from_path varchar[255]
from_revision varchar[255]
revision varchar[255]
branch  varchar[255]

Indices: Primary (ID),
              changeset_id set to INDEX BTREE

All on latin1 charset based on a bit of info from http://forge.mysql.com/wiki/Top10SQLPerformanceTips

The Table Engine is InnoDB Pack Keys is set to Default (only packs char varchar)

All the other options are turned off.

Whats the best way to optimize this? (Bar Truncate ;o) )

Answer 1

It depends entirely on your read and write characteristics, i.e., the queries you're making, and how often you're writing to it.

The way to optimize for writing is to minimize the number of indexes. Ideally, you use what in MS SQL server would be the "clustered index" with a monotonically incrementing key, ensuring that you write new records to the end of the table, and you write no other separate index. Better yet, even, is to skip the DBMS and write to a plain old log file of some sort, if you don't need any transactional capability.

For queries, well, that can get as complex as you like. Do keep in mind, though, that if you need any significant amount of data from the table for a query (i.e., it's more than just looking up a single record based on a key), table scans may not be such a bad thing. Generally, if you're examining more than 3-5% of the contents of a table, a table scan will be very fast. Again, for this, a plain old file will probably be faster than a DBMS.

If you have to optimize for both, consider optimizing for writing, and then making a copy on a regular basis that you optimize for queries, and doing the queries against the copy.

Answer 2

There are some general optimization techniques for mysql: the first would be make sure your datatypes fit the ABCs (see here). Going over then from top to bottom, ID and changeset_id look good, action should probably be a char[1] instead of a varchar (nullable if you can leave it blank (and in general, make sure your nullable is set correctly on other fields)). As for the 5 other fields (which depending on size would probably dominate the table), are strings the correct datatype? (I'm guessing yes with path, from_path, branch, but maybe revision should be a number (I'm guessing it isn't so it supports git or something))

Also, they look like normalization targets, especially since a "paths" and "revisions" table would normalize four of them (here's a basic tutorial, if you need it)

Answer 3

What du you want to optimize? insert performance or select?

i suggest adding indexes for coulumns you use in your selects.