MySQL optimization

[John Glenn]

I'm looking for recomendations on improving system performance. Using the SQL command below on a MySQL server I find result times averaging three seconds. Being very inexperienced with database programming I would appreciate any comments on whether this is expected behaviour, or where my design might improve. The details of my system are below.


The data I'm looking for starts with tblItems which holds a list of Items we're looking to buy. I want the name of the item from tblStock, and statistics on prices we've found in our history (if they exist). I've never done a multi table join before and this is what I've come up with:


300 Mhz, 32 MB RAM
CPU: 65% idle, RAM: 12MB Free
OS: Slackware Linux 8.1 (2.4.18)
Mysqld Ver 3.23.53a for pc-linux-gnu on i686

mysql> select fldName, fldQuantity, fldTotalCost, tblntItems.fldObjectID, avg(fldQuote), count(fldQuote), max(fldQuote), min(fldQuote) from tblStock, tblntItems left join tblntQuotes on tblntItems.fldObjectID = tblntQuotes.fldObjectID where tblntItems.fldObjectID = tblStock.fldObjectID group by fldObjectID;



mysql> show fields from tblStock;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| fldObjectID | int(11) | YES | | NULL | |
| fldName | varchar(255) | YES | | NULL | |
| fldCost | int(11) | YES | | NULL | |
| fldRetail | int(11) | YES | | NULL | |
| fldUpdate | varchar(255) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
aprox 7000 items.



mysql> show fields from tblntQuotes;
+-------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------+------+-----+---------+-------+
| fldObjectID | int(11) | YES | | NULL | |
| fldDate | date | YES | | NULL | |
| fldSource | int(11) | YES | | NULL | |
| fldQuote | double | YES | | NULL | |
+-------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
approx 130 records.


mysql> show fields from tblntItems;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| fldObjectID | int(11) | YES | | NULL | |
| fldQuantity | int(11) | YES | | NULL | |
| fldTotalCost | double | YES | | NULL | |
+--------------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)
approx 100 records.


Regards,

John Glenn


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php