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.

MySQL explains the filtered column jumps 4,100 with index

My Query:

EXPLAIN EXTENDED SELECT  `artwork`.`id` ,  `artwork`.`added`
FROM  `artwork`
ORDER BY  `artwork`.`added` DESC
LIMIT 0 , 6

When I added an index on "added" to avoid using filesort and use index instead the output of explained went from

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE artwork ALL NULL NULL NULL NULL 302 100.00 Using filesort

to

id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE artwork index NULL added 4 NULL 6 5033.33

and I'm concerned about the filtered going up approximently 4,100 - I can't find on Google what filtered means

---

Hey, it's actually good news. It shows how many rows your limitations are removing from the result set. In this case, that would be your LIMIT statement. See the manual:

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, rows shows the estimated number of rows examined and rows × filtered / 100 shows the number of rows that will be joined with previous tables. This column is displayed if you use EXPLAIN EXTENDED.

Meaning of & ldquo; Select optimized tables & rdquo; in MySQL Explain plan

What is the meaning of Select tables optimized away in MySQL Explain plan?

explain select count(comment_count) from wp_posts;

+----+-------------+---------------------------+-----------------------------+
| id | select_type | table,type,possible_keys, | Extra                       |
|    |             | key,key_len,ref,rows      |                             |
+----+-------------+---------------------------+-----------------------------+
| 1  | SIMPLE      | all NULLs                 | Select tables optimized away|
+----+-------------+---------------------------+-----------------------------+
1 row in set (0.00 sec)

Note: explain plan output edited for legibility.

---

It means you have done a query that does nothing more than count the number of rows in a table, and that table is a MyISAM table. MyISAM tables are stored with a separate row count, so to do this query MySQL doesn't need to look at any of the table row data at all. Instead it immediately returns the pre-calculated row count. Hence the table access is ‘optimized away’ and the query is lightning-fast.

The same won't happen on other storage engines in MySQL such as InnoDB. But really, you want to be using InnoDB and not MyISAM in most cases for a variety of other reasons. (And even without the row count optimisation this kind of query is very, very fast.)

select count(comment_count) from wp_posts;

Is that what you really meant to do? That's the same as just SELECT COUNT(*)...(assuming comment_count can't be NULL, which it can't be or you wouldn't have got the optimisation). If you want a total of the comment_count​s you should be using SUM(comment_count), and you won't get the ‘optimized away’ behaviour.