MySql optimisation questions

[ElliotP]

Hi,

I work for an internet based company, we have a website which is doing far better than any of us thought it would do, the site has just reached around 50,000 members.

When the database was designed, we didn’t take into account optimisation of any kind – therefore mysql cpu usage has gone through the roof, and the site has slowed to a crawl.

I have started to re-design the database recently, and have taken advice from others on best practises, but I have a few questions that are playing on my mind, I would appreciate if someone could answer them for me.

Primary keys – why is indexing and querying tables best done by integer? I know it is, and I know it’s the way to do it – but could someone give me a reason why an index integer is faster to lookup than an indexed text field? (in this case it would be a userid and a username) – I’m looking for the technical reason as to why its quicker?

Second question – I’ve been told that zerofilling keys (int(8)’s in this case) helps mysql search the table quicker and more efficiently – is this true, and can anyone offer an explanation of why its quicker?

Third question – We currently have a message table – it stores messages sent between users, the message bodies are currently being stored as blobs, I have been advised to change this to text field types. Will there be any performance advantages of text over blob field types, when retrieving and writing records?

Forth and final question – We currently have a large member table (aound 43 fields) which stores usernames, passwords, profile options, subscription details etc, it is the heart of the database. I realise this is quite an open ended question – but will performance be gained by splitting this table into smaller logical tables? (eg log-in details in one, profile details in another, subscription details in another) and using JOIN queries to retrieve data from multiple tables at once? Or is best performance to keep it in a single table?

Apologies for the amount of questions and length of post, any advice greatly appreciated.

Elliot.

[Rawhide]

I'm not a MySQL programmer (usually). I'm a SQL Server dba, but your questions are generic enough that they are pretty much universally true.

1. First off, it is simply easier to find an integer than it is to find a word. The database already knows what all of the possible values are and therefore knows how to find them, particularly when they are indexed.

Secondly, size counts. The smaller the field is in bytes, the less the database has to read to find a value. Which could you scan quicker, a list of 50,000 50 letter words or a list of 50,000 2 letter words?

And the smaller the field, the smaller the index as well. So not only is scanning the field a lot quicker, so is scanning the index.

2. SQL Server doesn't have a zerofill option, so i'm not familiar enough with it to answer this one. I can't find anything online that says one way or another.

3. Yes. ADO, OLEDB, etc., can't read large Blob objects in a single pass. They have to read it in chunks and then append the pieces together. The same is true for the reverse, you have to write it in chunks if it is above a certain size.

4. There's no one single answer for this question that is always the right one. It depends on a lot of factors. However, splitting a table just for the sake of splitting it will not give you a performance gain.

What generally gives you a performance gain is where you can break the data down into logical blocks of data. For example, if one of the bits of data you collect is the user's home country, you would gain performance by splitting out a lookup table with a countries in it and only storing the ID of the user's country in the main table.