Optimizing a Query
How can I optimize the following 3-way JOIN SQL query given then following schema:
(All tables are MyISAM)
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)
Subject
Views
Written By
Posted
Optimizing a Query
2680
March 12, 2007 08:45PM
Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.