0

Hi and thanx for reading my post i am having a little trouble learning my database in mysql. Now i have it set up already but recently, but i had another person tell me my members table is slow and useless if i intend to have a lots of members!

I have looked it over a lot of times and did some google searches but i don't see anything wrong with it, maybe because i am new at it? can one of you sql experts look it over and tell me whats wrong with it please :)

--
-- Table structure for table `members`
--

CREATE TABLE IF NOT EXISTS `members` (
  `userid` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',
  `password` longtext,
  `email` varchar(80) NOT NULL DEFAULT '',
  `gender` int(1) NOT NULL DEFAULT '0',
  `ipaddress` varchar(80) NOT NULL DEFAULT '',
  `joinedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `acctype` int(1) NOT NULL DEFAULT '0',
  `acclevel` int(1) NOT NULL DEFAULT '0',
  `birthdate` date DEFAULT NULL,
  `warnings` int(1) NOT NULL DEFAULT '0',
  `banned` int(1) NOT NULL DEFAULT '0',
  `enabled` int(1) NOT NULL DEFAULT '0',
  `online` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `emailadd` (`emailadd`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

--
-- Dumping data for table `members`
--

It's going to be a site for faqs/tips for games, i do expect to get lots of members at one point later on but i thought i would ask to make sure it's all ok, thanx again peace.

Wooble
  • 87,717
  • 12
  • 108
  • 131
DeathWitchNMayo
  • 1
  • What are you using the `ipaddress` field for? – Jacob Jul 23 '11 at 08:32
  • I am using it several different things, one i am checking it against the one all ready stored in my database so if it don't match and they reach the 5 "try" limit on the login page it will auto ban them. – DeathWitchNMayo Jul 23 '11 at 22:29

3 Answers3

3

Did the other person explain why they think it is slow and useless?

Here's a few things that I think could be improved:

email should be longer - off the top of my head, 320 should be long enough for most email addresses, but you might want to look that up.

If the int(1) fields are simple on/off fields, then they could be tinyint(1) or bool instead.

As @cularis points out, the ipaddress field might not be the appropriate type. INT UNSIGNED is better than varchar for IPv4. You can use INET_ATON() and INET_NTOA() for conversion. See:

Best Field Type for IP address?
How to store IPv6-compatible address in a relational database

As @Delan Azabani points out, your password field is too long for the value you are storing. MD5 produces a 32 character string, so varchar(32) will be sufficient. You could switch to the more secure SHA2, and use the MySQL 'SHA2()' function.

Look into using the InnoDB database engine instead of MyISAM. It offers foreign key constraints, row-level locking and transactions, amongst other things. See Should you move from MyISAM to Innodb ?.

Community
  • 1
  • 1
Mike
  • 21,301
  • 2
  • 42
  • 65
  • Hi @Mike thanx for all the info i will check into those things and make it better, as for MD5 it's the only thing i could get working i don't know why but when ever i tried to set up the SHA2() it would never save the key to the password table? It kept coming out empty :( – DeathWitchNMayo Jul 23 '11 at 22:31
1

I don't think it's necessarily slow, but I did notice that among all other text fields where you used varchar, you used longtext for the password field. This seems like you are going to store the password in the database -- don't do this!

Always take a fixed-length cryptographic hash (using, for example, SHA-1 or SHA-2) of the user's password, and put that into the database. That way, if your database server is compromised, the users' passwords are not exposed.

Delan Azabani
  • 79,602
  • 28
  • 170
  • 210
  • Hi yeah i am storing the password in my database but before it is put into it i have it MD5 encrypted is that what you mean? i tried to set up sha256 but i don't know how... So i just have it stored like this: $password = md5($password); – DeathWitchNMayo Jul 23 '11 at 08:32
  • 1
    @Death: MD5 is not an encryption, it's a hash function. Basically, don't do this yourself, use a library like `bcrypt`, as you're likely to get it wrong and leave your passwords vulnerable to being discovered in the event that your database gets compromised. – Kerrek SB Jul 23 '11 at 09:01
  • Hi @Kerrek SB i am working on trying to add SHA2() to the join page today after i get home, i was told that MD5 was very secure and safe but now i am not so sure. I searched google and seen a MD5 Decrypt-er for it and i am scared to use it now. – DeathWitchNMayo Jul 23 '11 at 22:39
  • You better be scared, and you really better not try to sell your homebrew solution to anyone! :-) I suggest first learning some fundamentals of cryptography, and then move on to fundamentals of authentication systems and how to handle password-based authentication. – Kerrek SB Jul 23 '11 at 22:41
  • Wait what @Kerrek SB what are you talking about (sell your homebrew solution)?? it's not for sale it is my personal site not for business just cheat codes. – DeathWitchNMayo Jul 24 '11 at 02:09
  • @Death: Err... OK, maybe this conversation got away from me a little. Anyway, here's an interesting [article](http://www.openwall.com/articles/PHP-Users-Passwords) on managing passwords. – Kerrek SB Jul 24 '11 at 02:12
  • Oh a little XD @Kerrek SB thanx for the link i have been working hard on it and i must say the site is looking sexy just like me :) jk i got my join page all done and i was able to add the sha-2 it worked finally after 1 billion tutorials. – DeathWitchNMayo Jul 24 '11 at 17:49
1

Apart from what @Delan said, I noted that;

  1. JoinedOn column defined as ON UPDATE CURRENT_TIMESTAMP. If you need to maintain only the date joined, you should not update the field when the records been updated.
  2. IPAddress column is VARCHAR(80). If you store IPv4 type IP addresses, this will be too lengthy.
  3. Empty string ('') as DEFAULT for NOT NULL columns. Not good if intention is to have a value (other than '') on the field.
  4. Empty string ('') as DEFAULT for UNIQUE Fields. This contradicts the contraints enforced if your intention is to have a Unique Value (other than '').
It's actually me
  • 492
  • 2
  • 9
  • Hi @Kosala Nuwan i see that now thanx for pointing it out i am dumb some times, so i should just leave out the DEFAULT '' part? – DeathWitchNMayo Jul 23 '11 at 22:35