Hi, It seems that you don't have any index on your tables. You should at least create an index on flObjectID in all tables. You should also use 'explain' on your query to make sure that indexes are properly used.
Hope this helps Joseph Bueno John Glenn wrote: > > > 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
