[RESOLVED] sensible size for a mysql table

christillis

I've estimated 8000 entries per year in a table, the columns are as follows:

int(10)
int(2)
int(3)
date(dd-mm-yyyy)
char(300)
decimal(8,2)
deimale(8,2)
char(3)

Would this be too much? Look forward to any suggestions.

Many thanks,

Chris

jazz_snob

Provided you have proper indexes that should be no problem. At work we have a table w/ over 7 million records, and I think its about 30 columns per record. Indexes are the key. What they indexes will be depends on the queries you will actually run. The mysql site has a nice discussion on using indexes: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Piranha

A couple of thousand records would be no problem at all, with or without indexes. With millions of records it is another issue.

Btw, why do you use char(300), it is not possible in mysql. Read a bit more here.

And I don't know about the date(dd-mm-yyyy) either. I haven't found any example of it being possible.

christillis

jazz_snob (unusual 'handle'!)

Thanks for assuring me, much appreciated! I haven't set up indexes yet, although I have investigated them and will apply them before it goes 'live'. I'm going to have a good read of that link. Thank you mate and hope Cali' is well!

Piranha

Thank you for pointing out something I should have known! char(255) it is then! It's going to be a 'description' field where people say what they did, so it'll be a long string. Thanks for supplying the link, very much appreciated.

Sorry, reverse typo, I meant yyyy-mm-dd 🙂 I've not fully educated myself on the subject of dates in mysql/php, but I've got the info and it's just a case of reading :eek:

I'm probably designing my database/user access in an odd way, I'm trying to get the very basics working from the user starting point to the end point. Instead of doing it in stages. Oddly though, I'm finding AutoCAD really helpful for drawing up the structure of it. Having used AutoCAD virtually all of my employable life, I'm able to think and change things very easily. Saves me learning yet another program!

Thanks for all your help guys, much appreciated.

Piranha

christillis;10885757 wrote:
Thank you for pointing out something I should have known! char(255) it is then! It's going to be a 'description' field where people say what they did, so it'll be a long string. Thanks for supplying the link, very much appreciated.

First, you should not use char(255), you should use varchar(255). You should only use char when you know that a string will be exactly x characters, otherwise you should use varchar.

Second, if it is a description field it could be pretty long, possibly longer than 255 characters. Use some text type instead, it is a better way 🙂

christillis

Piranha;10885759 wrote:
First, you should not use char(255), you should use varchar(255). You should only use char when you know that a string will be exactly x characters, otherwise you should use varchar.

Second, if it is a description field it could be pretty long, possibly longer than 255 characters. Use some text type instead, it is a better way 🙂

Thank you again Piranha. Very fair point, I've become muddled with reading and thought char would be best, but varchar is really. Some entries might just be "Fuel", other might be, "went to site, surveyed farm shed and then had a bacon and egg sandwich with white bread".

I'll ask the powers that be how much space they think they'll need! I was just trying to minimise the size of the DB really.

All the best!
Chris