MySQL Forums
Forum List  »  Optimizer & Parser

Optimizing a Query
Posted by: Drew Kutcharian
Date: March 12, 2007 08:45PM

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)

Options: ReplyQuote


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.