My query does not use my indexes, how do I use explain plan and fix this slow query with MySQL

I have this query that is running slow (16 seconds), it only has 44085 records in the biggest table. Any suggestions or anything that sticks out?

thanks for any 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`
FROM virtual_alerts_users u
LEFT JOIN virtual_alerts_cons c ON c.referid = u.localConID
WHERE u.userID = 9581

When i do an explain i get::

  id  |  select_type  |  table  |  type  |  possible_keys  |  key  |  key_len  |  ref  |  rows  |  extra
  1   |  SIMPLE       |  u      |  ALL   |  Index 3        | null  |  null     |  null |  459   |  Using where
  1   |  SIMPLE       |  c      |  ALL   |  null           | null  |  null     | null  |  44085 |

The tables look 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, but you do have a combined index on userID and referid.

To force MySQL to use that, change your join condition to:

LEFT JOIN
    virtual_alerts_cons c
ON
    c.referid = u.localConID
        AND
    c.userId = u.userID

Alternatively, you could create an additional index on referid.