leo Posted November 25, 2012 Share Posted November 25, 2012 Hi i have a slight problem, i understand how to create and index tables in mysql, but i have this table creation here below... yes the user_id is primary and the username and email are unique, but i do not understand the last line the, INDEX login (username, pass), how will this be used? is login even a column in this database table once this key has been assigned, will taking this off give me a less secure login? what does is actually do? Kind regards CREATE TABLE users ( user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, lang_id TINYINT UNSIGNED NOT NULL, time_zone VARCHAR(30) NOT NULL, username VARCHAR(30) NOT NULL, pass CHAR(40) NOT NULL, email VARCHAR(60) NOT NULL, PRIMARY KEY (user_id), UNIQUE (username), UNIQUE (email), INDEX login (username, pass) ); Link to comment Share on other sites More sharing options...
leo Posted November 25, 2012 Author Share Posted November 25, 2012 i have been looking through the book and i am struggling to find were this INDEX login (username, pass) being used within the php, i think i am expecting to see some form of combination of some sort. Link to comment Share on other sites More sharing options...
Antonio Conte Posted November 25, 2012 Share Posted November 25, 2012 It's for speed. If certain combinations are commonly looked up, assigning an index will speed up that lookup. It has no other functionality than that. Link to comment Share on other sites More sharing options...
Edward Posted November 26, 2012 Share Posted November 26, 2012 Also regarding this post in the Yii Book in your Yii Book Larry you put UNIQUE INDEX username_UNIQUE (username ASC), UNIQUE INDEX email_UNIQUE (email ASC) in the username table so why not INDEX login (username, pass) Should this be added as well or as those two index's already enough for putting an index on username and email when someone is logging in? Also why use: date_entered TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, and not just DATE? Do we need the time in seconds exactly looking back, what is the reason for this? Sorry for all the questions. (Ah, because you can use it to minus this timestamp from other so see total time it was there) Link to comment Share on other sites More sharing options...
Larry Posted November 26, 2012 Share Posted November 26, 2012 You can add that login index, if you want. I didn't in part because logins are done differently in Yii than you would in a non-framework site. Second, I use TIMESTAMP for markers like date_entered and date_modified in order to most precisely pinpoint them. You'll have multiple records per day, and it'd often be nice to know which came first. 1 Link to comment Share on other sites More sharing options...
Edward Posted November 26, 2012 Share Posted November 26, 2012 Okay thanks for that, I have switched over from datetime to timestamp because they are more useful with php functions. I also read online that timestamps 32bit that finish in the year 2037 would most probably be extended to 64bit to buy them more time. This gives me a little more confidence in using them. Link to comment Share on other sites More sharing options...
Recommended Posts