Hi All,
How can I optimize the following 3-way JOIN SQL query given then following
schema:
SELECT
ORDER.ID, ORDER.STATUS, ORDER.ORDER_TIME, ORDER_ITEM.ID, ORDER_ITEM.QUANTITY,
PRODUCT.SIZE, PRODUCT.SHAPE, PRODUCT.PAPER, PRODUCT.TURNAROUND
FROM
ORDER, ORDER_ITEM, PRODUCT
WHERE
ORDER.ID = ORDER_ITEM.ORDER_ID AND
ORDER_ITEM.PRODUCT_ID = PRODUCT.PRODUCT_ID AND
ORDER.STATUS = status AND
ORDER.TIME > 'startTime' AND
ORDER.TIME < 'endTime' AND
ORDER_ITEM.QUANTITY = quantity AND
PRODUCT.SIZE = 'size' AND
PRODUCT.SHAPE = 'shape' AND
PRODUCT.PAPER = 'paper' AND
PRODUCT.TURNAROUND = 'turnaround'
ORDER BY ORDER.ORDER_TIME DESC
LIMIT start, offset;
[ORDER TABLE]
row count = 350,544 (read/write. mostly read)
+----------------+---------------------+------+-----+---------------------+
| Field | Type | Null | Key | Default |
+----------------+---------------------+------+-----+---------------------+
| ID | int(10) unsigned | NO | PRI | 0 |
| STATUS | tinyint(3) unsigned | NO | MUL | 0 |
| ORDER_TIME | datetime | YES | | NULL |
[ORDER_ITEM TABLE]
row count = 548,456 (read/write. mostly read)
+----------------+---------------------+------+-----+---------------------+
| Field | Type | Null | Key | Default |
+----------------+---------------------+------+-----+---------------------+
| ID | int(10) unsigned | NO | PRI | 0 |
| ORDER_ID | int(10) unsigned | NO | PRI | 0 |
| PRODUCT_ID | int(10) unsigned | YES | | 0 |
| QUANTITY | int(10) unsigned | YES | | NULL |
[PRODUCT TABLE]
row count = 56,641 (static content, hardly ever changes)
+-----------------------+----------------------+------+-----+---------+
| Field | Type | Null | Key | Default |
+-----------------------+----------------------+------+-----+---------+
| PRODUCT_ID | int(10) unsigned | NO | PRI | 0 |
| SIZE | varchar(50) | YES | | NULL |
(indexed)
| PAPER | varchar(50) | YES | | NULL |
(indexed)
| TURNAROUND | varchar(50) | YES | | NULL |
(indexed)
| SHAPE | varchar(50) | YES | | NULL |
(indexed)
Thanks,
Drew
