sql - My query is not using my indexes, how do i use explain plan and fix this slow query with MySQL -

sql - My query is not using my indexes, how do i use explain plan and fix this slow query with MySQL -

i have query running slow (16 seconds), has 44085 records in biggest table. suggestions or sticks out?

thanks help

select u.`vid`, u.`userid`, u.`localconid`, u.`lastran`, u.`laststatus`, u.`lastmessage`, u.`active` ,u.`autorundaily`, u.`autorunmonthly`, u.`fileslocation` ,c.`conid`, c.`fname`, c.`lname`, c.`homephone`, c.`cellphone` , c.`email` , date_format(u.`lastran`,'%d/%m/%y %k:%i') lastranformatted, u.`retrys` virtual_alerts_users u left bring together virtual_alerts_cons c on c.referid = u.localconid u.userid = 9581

when explain get::

id | select_type | table | type | possible_keys | key | key_len | ref | rows | 1 | simple | u | | index 3 | null | null | null | 459 | using 1 | simple | c | | null | null | null | null | 44085 |

the tables like::

create table `virtual_alerts_users` ( `vid` int(11) unsigned not null auto_increment, `userid` int(11) not null default '0', `localconid` varchar(10) null default null, `encrpytpw` varchar(100) null default null, `lastran` timestamp null default null, `laststatus` int(11) null default null, `lastmessage` text null, `active` tinyint(4) not null default '0', `autorundaily` tinyint(4) not null default '0', `autorunmonthly` tinyint(4) not null default '0', `fileslocation` varchar(512) null default null, `retrys` tinyint(4) null default '0', primary key (`vid`), unique index `index 2` (`localconid`), index `index 3` (`userid`) )

-

create table `virtual_alerts_cons` ( `conid` int(11) unsigned not null auto_increment, `userid` int(11) unsigned not null default '0', `vid` int(11) unsigned not null default '0', `fname` varchar(50) null default null, `lname` varchar(50) null default null, `referid` varchar(10) null default null, `level` varchar(2) null default null, `status` varchar(2) null default null, `lang` varchar(15) null default null, `homephone` varchar(15) null default null, `cellphone` varchar(15) null default null, `address` varchar(255) null default null, `email` varchar(255) null default null, `birthday_mon` tinyint(4) null default '0', `birthday_day` tinyint(4) null default '0', `anv_mon` tinyint(4) null default '0', `anv_day` tinyint(4) null default '0', `anv_cnt` tinyint(4) null default '0', `lasthash` bigint(20) null default '0', `lastupdated` timestamp null default current_timestamp on update current_timestamp, primary key (`conid`), unique index `index 3` (`userid`, `referid`), index `index 2` (`userid`), index `index 4` (`vid`) )

you have no index on referid in virtual_alerts_cons, have combined index on userid , referid.

to forcefulness mysql utilize that, alter bring together status to:

left bring together virtual_alerts_cons c on c.referid = u.localconid , c.userid = u.userid

alternatively, create additional index on referid.

mysql sql explain-plan