This query below is running REALLY slow. The indexes and EXPLAIN result are below.
Last week this query was running in <20 sec, now it takes so long (>20 mins). No I
reloaded all the data from the oe-invoice table over the weekend. as I do every
weekend. I have noticed that sometimes the query performance to into the weeds for no
aparent reason. I did an myisamchk --recovery on the v7_oe-invoice.MYI file, but did
not help.
I noticed it has a NULL on all of the cardinality fields in the show index command.
Does this mead anything?
SELECT
v7_oe_invoice1.`cust-num`, v7_oe_invoice1.`cust-site`, v7_oe_invoice1.`whs-num`,
v7_oe_invoice1.`tax-code`, v7_oe_invoice1.`post-date`, v7_oe_invoice1.`inv-merch`,
v7_cust1.`cust-job-num`, v7_cust1.`cust-name`, v7_cust1.`cust-transfer`,
v7_tax1.`tax-desc`
FROM
`v7_oe-invoice` v7_oe_invoice1,
`v7_cust` v7_cust1,
`v7_tax` v7_tax1
WHERE
v7_oe_invoice1.`cust-num` = v7_cust1.`cust-num` AND
v7_oe_invoice1.`tax-code` = v7_tax1.`tax-code` AND
v7_cust1.`cust-transfer` = 0 AND
v7_cust1.`cust-job-num` = 0 AND
v7_oe_invoice1.`post-date` >= {ts '2003-12-01 00:00:00.00'} AND
v7_oe_invoice1.`post-date` < {ts '2003-12-22 00:00:01.00'} AND
v7_oe_invoice1.`whs-num` = '8'
ORDER BY
v7_oe_invoice1.`tax-code` ASC,
v7_oe_invoice1.`cust-num` ASC
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
| v7_oe-invoice | 1 | idx-oei-oet | 1 | oe-tran-num | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-csn | 1 | cust-num | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-slc | 1 | slm-code | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-cjn | 1 | cust-job-num | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-csi | 1 | cust-site | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-pod | 1 | post-date | A
| NULL | NULL | NULL | |
| v7_oe-invoice | 1 | idx-oei-whn | 1 | whs-num | A
| NULL | NULL | NULL | |
+---------------+------------+-------------+--------------+--------------+------
-----+-------------+----------+--------+---------+
and the Explain
+
| v7_cust1 | ref | idx-cus-csn,idx-cus-cjn,idx-cus-cut | idx-cus-cjn |
5 | const | 14818 | where used; Using temporary; Using filesort
|
| v7_oe_invoice1 | ref | idx-oei-csn,idx-oei-pod,idx-oei-whn | idx-oei-csn |
9 | v7_cust1.cust-num | 10 | where used
|
| v7_tax1 | ALL | NULL | NULL |
NULL | NULL | 258 | where used
|
+----------------+------+-------------------------------------+-------------+---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
