I have one table with 2 unique indexed fields named "username" and "emailid". when I have fire one query on this table with OR condition like
SELECT * FROM tableName where (username = 'aaaa' OR emailid = 'aaaa')
the explain statement shows me for above query is
"Using union(UX_EMailID,UX_UserName); Using where"
under "SIMPLE" as select_type.
Is it ok for performance, or something is wrong. One more way to do this with me. first call with one field and if no record found then try for second field like as follows.
IF EXISTS (SELECT 1 FROM tableName where username = 'aaaa' ) THEN //do code ELSE IF EXISTS (SELECT 1 FROM tableName where emailid = 'aaaa' ) THEN //do code ELSE //throw error END IF; END IF;
So, Now I have confusion, which is better way for performance.
table: usermst ~~~~~~~~~~~~~~ id int //primary key username varchar(20) //unique index emailid varchar(50) //unique index
id: 1, select_type: 'SIMPLE' table: 'usermst' type: 'index_merge' possible_keys: 'ux_username,ux_emailid' key: 'ux_emailid,ux_username' key_len: '153,63' ref: null rows: 2 Extra: 'Using union(ux_emailid,ux_username); Using where'
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT * FROM tableName WHERE ( tableName.username = 'aaaa' OR tableName.emailid = 'aaaa' )