List:General Discussion« Previous MessageNext Message »
From:Joseph Bueno Date:December 17 2002 9:47am
Subject:Re: MySQL optimization
View as plain text  
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
> 
> 

Thread
MySQL optimizationJohn Glenn17 Dec
  • Re: MySQL optimizationJoseph Bueno17 Dec