Hello, I am facing some problems to identify the advantages or disadvantages of the use of INDEXes (a.k.a. VIEWS in other DBMS environments, please correct me if am wrong), this is the scenario:
I have a logging system that retrieves the username and password to grant access to the user, this is the query that I am used to: Query #1 $sel_user = "SELECT username, pwd FROM profile WHERE sts_flg=2 AND username = $user_name"; And it works very well, but my manager suggested me the use of an INDEX (VIEW), so after modifying the DB script, so it looks like this: CREATE TABLE profile( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, fname VARCHAR(20) NOT NULL, mname VARCHAR(20) NOT NULL DEFAULT '', lname VARCHAR(20) NOT NULL, username VARCHAR(20) NOT NULL, pwd VARCHAR(100) NOT NULL, birth_dt DATE NOT NULL DEFAULT '0000-00-00', country TINYINT(3) UNSIGNED NOT NULL, crt_dt DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', lupt_dt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, sts_flg TINYINT(1) UNSIGNED NOT NULL DEFAULT 0, /*1=Pending, 2=Active, 3=Suspended, 4=Suspended(Review), 5=Inactive*/ PRIMARY KEY(`id`), INDEX prof_login(`username`,`pwd`,`sts_flg`), UNIQUE username(`username`) ); I am using this query to retrieve the data: Query #2 $sel_user = "SELECT username,pwd FROM profile USE INDEX (prof_login) WHERE sts_flg=2 AND username = $user_name"; So I can't see any direct advantage of the use of #2 instead of #1, except that am using more SQL code ;), I've read about MySQL indexes on the manual but haven't founded the answer yet, so these are the questions: Which is the best way to do the query #1 or #2? Why query #1 or #2 is the best? Is there a more optimum way to do this? Many, many thanks! -- Iván Alemán ~ [[ m o f o ]] ~ Debian (SID) -----BEGIN GEEK CODE BLOCK----- Version: 3.12 G!>GCM d+ s: a? C+++ UL++ P L+>+++$ E--- W++>+ N* o--- K- w O- M+ V-- PS++ PE-- Y PGP+>++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z*>*$ ------END GEEK CODE BLOCK------ bonovoxmofo.blogspot.com 6EA4 BC00 420B 2087 C546 32D6 1258 3492 4220 7E8B
