The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custid
s. How can I make it run faster?
Setup:
Mac OS X 10.7.5
MySQL 5.5.27
Query:
select
pn as productId,
qty,
invno as invoiceId,
invdate as invoiceDate
from
(select
trim(pn) as pn,
qtyord as qty,
oeinvch.INVNO,
oeinvch.invdate
from
(select
invno, invdate
from
W_OEINVCH
where
(invdate between '2013-08-01' and '2013-11-06') and custid = '13651') as oeinvch
join W_OEINVCD ON oeinvch.invno = W_OEINVCD.invno) as QtyOrdered
Explain Output:
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 38
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: <derived3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: W_OEINVCD
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1130159
Extra: Using where; Using join buffer
*************************** 4. row ***************************
id: 3
select_type: DERIVED
table: W_OEINVCH
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 102437
Extra: Using where
4 rows in set (0.58 sec)
UPDATE FROM EDZE'S ANSWER: 06/11/13
Avoiding derived tables. Execution time is increased now to ~4.2 secs.
Query:
SELECT
TRIM(W_OEINVCD.pn) as productId,
W_OEINVCD.qtyord as qty,
W_OEINVCH.invno as invoiceId,
W_OEINVCH.invdate as invoiceDate
FROM
W_OEINVCH
JOIN W_OEINVCD
ON W_OEINVCH.invno = W_OEINVCD.invno
WHERE
W_OEINVCH.invdate BETWEEN '2013-08-01' AND '2013-11-06'
AND W_OEINVCH.custid = '13651'
Explain output:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: W_OEINVCD
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1130159
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: W_OEINVCH
type: ref
possible_keys: invno
key: invno
key_len: 24
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
SHOW CREATE TABLE output:
*************************** 1. row ***************************
Table: W_OEINVCH
Create Table: CREATE TABLE `W_OEINVCH` (
`INVNO` char(8) NOT NULL DEFAULT '',
`INVDATE` date NOT NULL,
`SHIPDATE` date NOT NULL,
`OENO` char(8) NOT NULL DEFAULT '',
`CUSTID` char(8) NOT NULL DEFAULT '',
KEY `invno` (`INVNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
*************************** 1. row ***************************
Table: W_OEINVCD
Create Table: CREATE TABLE `W_OEINVCD` (
`invno` varchar(45) DEFAULT NULL,
`pn` varchar(45) DEFAULT NULL,
`qtyord` int(11) DEFAULT NULL,
FULLTEXT KEY `invnoIndex` (`invno`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Best Answer
Avoid derived tables, like ypercube mentioned.