CODEWITHSUNDEEP
mysqldatabasedatabase-designdata-structures
PHPLOVER
PHPLOVER

Reputation: 7257

Database optimization advice

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

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.

Upvotes: 0

Views: 260

Answers (4)

Nanne
Nanne

Reputation: 64399

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)

Upvotes: 2

Andrei Draganescu
Andrei Draganescu

Reputation: 387

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)

Upvotes: 2

Sean Milheim
Sean Milheim

Reputation: 47

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.

Upvotes: 0

Andre Hühn
Andre Hühn

Reputation: 37

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

Upvotes: -1

Related Questions