database performance/scalability questions

Good2CU

I’m developing a web application and would like to make it as scalable as possible before having to go to multiple servers etc. I’ve got a few questions I’m hoping I can get some feedback on before coding:

InnoDB vs. MyISAM
I don’t need transactions, but I will have people reading and updating separate records of a few main tables frequently. There’s also a small chance that someone would be trying to read a record at the same time someone else is trying to update that same record. It’s not vital that the reader get the most up to date record, but I don’t want to have data corrupted in this situation. My main concern is high performance at scale.

My understanding is that with MyISAM, only one query can be executed against a table at any time, which is not the case with InnoDB?

If you’re solely logging data with very few reads, are you better off with MyISAM?

Would it be more efficient to have a Boolean flag for a yes/no value that you can filter in a WHERE clause, or have a separate table that has only the x_id value for the “yes” records to query with WHERE EXISTS or WHERE NOT EXISTS? Does it change depending on how likely a row is to be yes or no?

One part of the application will be to have a table that is: tag_id & tag. Some lookups will be giving a textual tag in the url that will need to lookup the id, and some will be looking up the tag for an id elsewhere in the database. These will both be very frequent occurnces. Would it be better to have two separate tables with different primary keys, or to have a tag_id > tag table with a 3 or 4 character index on the tag field? In either case, there will be a lot more reads than writes, but A LOT of reads, which storage engine would be preferable?

If speed is much more important than storage space, should the index be only on n characters of tag, or just on the entire field, which will probably be varchar(50)?

Is a primary key automatically an index, or is there more you could do to a primary key to improve performance?

Likewise, if I have a compound key with 2 or 3 fields, is there anything else to do for maximum speed?

After doing some searches on things like “best practices for scalable databases” I get a lot of results for data warehouse-type applications, are there any good resources for more basic and relatively plain English best practices?

Thanks in advance

sneakyimp

As of MySQL 5.5, InnoDB is the default table type. Having just read the (which makes lots of comparisons to MyISAM) I'm of the opinion that if scaling is what you are after then InnoDB is probably a better choice. This opinion is not based on a particularly detailed understanding but I do recall reading something about repairing crashed tables which said that InnoDB uses an incremental approach somehow that results in shorter rebuild times whereas MyISAM has to rebuild indexes completely from scratch. If you are going to have huge tables, InnoDB should provide better availability.

RE your question about 'solely logging data' I don't really know. Any particular reason why you think MyISAM would be better?

The Boolean flag question is a very good one. Sounds like you've been doing some reading. I can speak with firsthand experience about this particular issue. I built a database that used a lot of boolean flags to indicate various things. When I began to test the system by populating it with random data, the boolean flags became a real performance problem because indexing just doesn't help them. It's fine if you have maybe 1 million records, but when you get to 100 million, it's going to crawl. It takes a lot of extra effort to maintain those other data sets, but it would be a wise choice if you are concerned about scaleability to large amounts of data -- and by 'large' i mean on the order of a million records in a table.

I can't really comment on your tag/tag_id question and am a little confused by what you are describing. I suggest you create a db with a few million fake records that are distributed roughly like your data is and run some tests. I don't see any good reason to have more than one table if you are just connecting textual ids to a primary key of some kind. One table should work just great to get one from the other.

As for n characters vs. the entire string, the question is likely to depend on the real life content of your DB. a 50-char index would be great if you had 50 bazillion records, but your index would be HUGE and any advantage you gain with the longer index might be nullified by the fact that your system has to page through the entirety of a gigantic index file and your hard disk would be given a serious workout to little real advantage. Not sure what else to say except experiment with some dummy data. I'd love to see it 🙂

I think a primary key means you get an index with it, but it would probably depend on the way in which you create the table. I usually use phpMyAdmin. When you specify a field as a primary key, you can see where it lists the indexes that there is an index corresponding to your primary key. I don't know if anything will stop you from deleting that index by clicking the red X though.

I have little experience with compound keys. I could be wrong, but I think the sequence of the fields in the index matters.

Not sure what sort of "data warehouse" applications you might have seen, but if I were you I would look at something like SimpleDB or BigTable or some other kind of cloud storage offering. These things are not really databases in the sense to which you are accustomed, but manage a lot of details for you and promise more or less infinite scaleability at the expense of things you are familiar with like JOINS (they are usually not relational!). They require a different mindset which I have not yet cultivated.

johanafm

sneakyimp;10975096 wrote:
I have little experience with compound keys. I could be wrong, but I think the sequence of the fields in the index matters.

They do. If you have an index over fields (A, B, C), then this index can also be used to retrieve rows WHERE A = 1 AND B = 2, but not for B = 3 AND C = 4. If you always retrieve data by the whole key, then this index is enough. If you also need to (often enough) retrieve data by a subset of size N of the key that does not include all first N parts of the key, create the needed variations such as (A, C) and/or (B, C).

The reason you do not create every conceivable key without thought is that it takes time to manage the index. Inserts, deletions and updates have to update indices as well. More indices means more time needed for an insert, update or delete query.

You will also be affected by the relation between reads (SELECTS) and writes. If you have only writes and not a single select ever, there is no need for indexing. If you create and fill your table with (lots of) data and never ever perform another insert/update/delete but will perform every possible variations of WHERE clause, then you might be served by having every conceivable index on the table.

A primary key is always unique and indexed. This continues to hold even if the primary key is compound.

johanafm

I'm not certain I understand your question about tags either. When you talk about possibly splitting up into two tables with differnet ids, are you actually talking about taking one table like

-- part of this table
id  tag
11  'apple'
12  'banana'
13  'lemon'

... and splitting it up into

id   tag_id
11   1
12   2
13   3

tag_id tag
1     'apple'
2     'banana'
3     'lemon'

If so, the answer is no - stick with one table. What you do is keep two indices. One you get by having id as primary key, the other you get by creating an index on the tag.
This way, if you query by WHERE = id... the index for id is used, and if you query by WHERE tag='apple' the index for tag is used.

johanafm

Also, if you are interested in not losing data, innoDB. You will however not get full text indexing (MySQL only has this for MyISAM) if this is needed. But InnoDB is ACID compliant while MyISAM isn't.
And if you need both ACID compliance and full text indexing, go with PostgreSQL since it gives you both and is still free (BSD style license iirc)