mysql left outer join,尝试使用LEFT OUTER JOIN优化MySQL查询

I've this query, which works fine except it takes a long while (7 seconds, with 40k records in the jobs table, and 700k in the wq table).

I tried an EXPLAIN and it says its looking at all the records in the job table, and not using any of the indexes.

I don't know how to tell MySQL that it should use the jobs.status field to filter the the records before looking up the wq table.

The objective of this, is to get all the records from jobs that have a status != 331, and also any other job which has a wq status of (101, 111, 151).

Query:

SELECT jobs.*

FROM jobs

LEFT OUTER JOIN wq ON (wq.job = jobs.id AND jobs.status IN (341, 331) AND wq.status IN (101, 111, 151))

WHERE ((wq.info is not NULL) or (jobs.status != 331 and ack = 0))

EXPLAIN output:

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE jobs ALL ack,status,status_ack NULL NULL NULL 38111 Using filesort

1 SIMPLE wq ref PRIMARY,job,status PRIMARY 4 cts.jobs.id 20 Using where

Table definitions:

CREATE TABLE jobs ( id int(10) NOT NULL AUTO_INCREMENT,

comment varchar(100) NOT NULL DEFAULT '',

profile varchar(60) NOT NULL DEFAULT '',

start_at int(10) NOT NULL DEFAULT '0',

data text NOT NULL,

status int(10) NOT NULL DEFAULT '0',

info varchar(200) NOT NULL DEFAULT '',

finish int(10) NOT NULL DEFAULT '0',

priority int(5) NOT NULL DEFAULT '0',

ack tinyint(4) NOT NULL DEFAULT '0',

PRIMARY KEY (id),

KEY start_at (start_at),

KEY status (status),

KEY status_ack (status,

ack) ) ENGINE=MyISAM AUTO_INCREMENT=2037530 DEFAULT CHARSET=latin1;

CREATE TABLE wq ( job int(10) NOT NULL DEFAULT '0',

process varchar(60) NOT NULL DEFAULT '',

step varchar(60) NOT NULL DEFAULT '',

status int(10) NOT NULL DEFAULT '0',

run_at int(10) NOT NULL DEFAULT '0',

original_run_at int(10) NOT NULL DEFAULT '0',

info varchar(200) NOT NULL DEFAULT '',

pos int(10) NOT NULL DEFAULT '0',

changed_at int(10) NOT NULL DEFAULT '0',

file varchar(60) NOT NULL DEFAULT '',

PRIMARY KEY (job,

process,

step,

file),

KEY job (job),

KEY status (status) ) ENGINE=MyISAM DEFAULT CHARSET=latin1

解决方案

Unfortunately mysql (and perhaps any dbms) cannot optimize expressions like jobs.status != 331 and ack = 0 because B-Tree is not a structure that allows to find fast anything that is-not-equal-to-a-constant-value. Thus you'll always get a fullscan.

If there were some better condition like jobs.status = 331 and ack = 0 (note on the fact that i've changed != to =) then it would be an advice to speed up this query:

split the query into 2, joined by UNION ALL

replace in one query LEFT JOIN to INNER JOIN (in the one that implies that wq.info is not NULL)