Database optimization advice

I have a table called members. I am looking on advice how to improve it.

• id : This is user id (unique) (auto increment) (indexed)
• status : Can contain 'activated', 'suspended', 'verify', 'delete'
• admin开发者_JAVA百科 : This just contains either 0 or 1 (if person is admin or not)
• suspended_note : If a members account is suspended i can add a note so when they try and login they will see the note.
• failed_login_count : basically 1 digit from 0 to 4, counts failed logins
• last_visited : unix timestamp of when they last visited site; (updated on logout) (i do this via php with time() )
• username : can contain from 3 to 15 characters (unique and indexed)
• first_name : can contain letters only and from 3 to 40 chars in length
• last_name : can contain letters only and from 2 to 50 chars in length
• email : can contain an email address (i use php email filter to check if valid)
• password : can contain from 6 to 10 chars in length and is hashed and contains fixed length of 40 chars in database once hashed
• date_time : unix timestamp (i do this via php with time() ). When user logs in
• ip : members ip on registration/logins
• activationkey : i use md5 and a salt to create a unique activation key; length is always 32 chars
• gender : either blank or male/female and nothing else.
• websiteurl: can add they site url;
• msn : can contain msn email address (use regular expression to match this)
• aim : aim nickname (use regular expression to match this)
• yim : yim nickname (use regular expression to match this)
• twitter : twitter username (use regular expression to match this)

suspended_note; first_name; last_name; date_time; ip; gender; websiteurl; msn; aim; yim; twitter can be null because on registration only username, email and password is required so those fields will be null until filled in (they are basically optional and not required) apart from ip which is taken on signup/login.

Could anyone tell me based on the information I have given how I can improve and alter this table more efficently? I would say I could improve it as I tend to use varchar for most things and am looking to get the best performance out of it.

I tend to do quite a few selects and store the user data in sessions to avoid having to query database every time. Username is unique and indexed like id as most of my selects compare have username in it with LIMIT 1 on my queries.

UPDATE:

I wanted to ask if I changed to enum for example how would I do a select and compare query for example in php for enum? I did look online but cannot find any example queries with enum being used. Also if I changed date_time for example to timestamp do I still use time() in php to insert the unix timestamp into date_time column database?

The reason I ask is I was reading one tutorial online that says when the row is queried, selected, updated etc MySQL automatically updates the timestamp for that row; is this true as I rather insert the timestamp using php time() in timestamp field. I use php time() already for date_time but use currently use varchar not timestamp.

Plus server time is in US and in php.ini I set it to UK time but I guess mysql would store it in the time on the server which again is no good as I want them in UK time.

Some tips:

• Your status should be an int connected to a lookup, or an enum.
• ditto for gender
• You could use a char instead of varchar. There is a lot of discussion available on that, but while varchar does help you cut down on the size, that is hardly a big issue most of the time. char can be quicker. this is tricky point though.
• safe your date_time as a timestamp. There is a datatype for that
• ditto for last_visited
• Your ip field looks a bit long to me.
• an int(5) can hold too much. So if your failed count is max 4, you don't need that big of a number! A tinyint can hold upt o 127 signed, or 255 unsigned.

A note from the comments:

You could probably normalize some fields: fields that update often, like failed_login_count, ip, last_visited could be in another table. This way your members table itself doesn't change as often and can be in cache

I agree with this :)

Edit: some updates after your new questions.

example how would I do a select and compare query for example in php for enum?

You can just compare it to the value as if it was a string. The only difference is that with an insert or update, you can only use the give value. Just use

SELECT * FROM table WHERE table.enum = "yourEnumOption"

changed date_time for example to timestamp do I still use time() in php to insert the unix timestamp into date_time column database?

You can use now() in mysql? (this is just a quick fromthetopofmyhead, could have a minor mistake, but:

INSERT INTO table (yourTime) VALUES (NOW());

reason I ask is I was reading one tutorial online that says when the row is queried, selected, updated etc MySQL automatically updates the timestamp for that row; is this true as I rather insert the timestamp using php time() in timestamp field. I use php time() already for date_time but use currently use varchar not timestamp.

You can use the php time. The timestamp does not get updated automatically, see the manual (http://dev.mysql.com/doc/refman/5.0/en/timestamp.html): you would use something like this in the definition:

CREATE TABLE t (
ts1 TIMESTAMP DEFAULT 0,
ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP)

First of all you should use mysql's built in field types:

• status is ENUM('activated', 'suspended', 'verify', 'delete');
• gender is ENUM('male','female','unknown')
• last_visited is TIMESTAMP
• suspended_note is TEXT
• failed login count is TINYINT(1) because you wouldnt have 10000 failed logins right - INT(5)

• date_time is DATETIME or TIMESTAMP

  1. add an index on username and password (combined) so that logins are faster
  2. index, unique email since you'll query by it to retrieve pwds and it should be unique

Also you might want to normalize this table and separate suspended_note, website, IP, aim etc to a separate table called profile. This way logins, session updates, pwd retrievals are queries ran in a much smaller table, and have the rest of the data selected only in pages where you need to have such data as the profile/member pages.

However this tends to vary a lot depending on how your app is thought out but generally its better practice to normalize.

You could probably normalize even more and have a user_stats table too: fields that update often, like failed_login_count, ip, last_visited could be in another table. This way your members table itself doesn't change as often and can be in cache. – Konerak 1 hour ago

VARCHAR is good but when you know the size of something like the activation key always is 32 then use CHAR(32)

Well first the basics..

IP should be stored as an unsigned INT and you would use INET_ATON and INET_NTOA to retrieve and store the IP.

Status could be an enum or a tinyint 1/0.

For last visited you could insert a unix timestamp using the mysql function UNIX_TIMESTAMP (Store this in a timestamp column). To retrieve the date you would use the FROM_UNIXTIME function.

Most answers have touched on the basics of using Enum's. However using 1 for Male and 2 for Female may speed up your application as a numeric field may be faster than an alphanumeric field if you do a lot of queries by that field. You should test to find out.

Secondly we would need to know how you use the table. How does your app query the table? Where are your indexes? Are you using MyISAM? Innodb? etc. Most of my recommendations would be based on how you app hits the table. The table is also wide so I would look into normalizing it as some others have pointed out.

• admin can be of type bit
• Activation key can be smaller