Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 custids. 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)
share|improve this question
    
Why the (double) nesting? Can you rewrite it without using derived tables? –  ypercube Nov 5 '13 at 15:46
    
Thanks @ypercube. Trying that now with edze's answer. –  max Nov 6 '13 at 5:54
    
Please add the SHOW CREATE TABLE output for both tables in the question. –  ypercube Nov 6 '13 at 6:12
    
Thanks @ypercube. I've added the SHOW CREATE TABLE output to my question. –  max Nov 6 '13 at 10:46
add comment

1 Answer

Avoid derived tables, like ypercube mentioned.

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'
share|improve this answer
    
That does make more sense and seems that it should run faster but its now taking ~4.2 secs to complete? I'll update my question with the explain output. –  max Nov 6 '13 at 5:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.