On 8/31/06, Brent Baisley <[EMAIL PROTECTED]> wrote:
hmmm, not sure why it's only scanning 89K records from the products table, I would think it would scan the whole table. It is scanning the entire items table, which I would think it wouldn't do.
Well, the query in question actually did do some work, the current row count IS 89k. My bad. The speed is still an issue though!
How about posting your "SHOW INDEX FROM items" result.
+-------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +-------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+ | items | 0 | PRIMARY | 1 | item_id | A | 123223 | NULL | | | BTREE | | | items | 0 | PRICE_INFO | 1 | product_id | A | NULL | NULL | | | BTREE | | | items | 0 | PRICE_INFO | 2 | supplier_id | A | 123223 | NULL | | | BTREE | | | items | 1 | retail_id | 1 | item_internal_id | A | 123223 | NULL | | | BTREE | | +-------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
Also, what are your memory settings? SHOW VARIABLES LIKE "%buffer_size"
+-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | bulk_insert_buffer_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | preload_buffer_size | 32768 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 2097144 | +-------------------------+----------+ The box has 1GB of physical RAM and 2GB in one swap partition. Thanks for the help so far! -- Kim Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
