Hello,
On Aug 31, 2006, at 5:12 AM, Kim Christensen wrote:
Hey list;
I posted this message under an earlier thread which touched the same
subject - but I realized my case could be slightly different, thus
posting a new thread here. Sorry for any inconvenience.
I have two tables, one of which is a list over products which all have
unique product id's (product_id). The other table, items, is a list
over the products' corresponding prices and stock info; one row per
each one of our supplier that has the item.
The problem is that your product_id is an INT in one table and a
VARCHAR in the other. This will cause MySQL to have to do
conversions of data types, which means it can't use indexes. Switch
product_id to be an INT and both tables and it should solve your
performance problems.
The culpr... ehrm, query:
DELETE products FROM products
LEFT JOIN items ON products.product_id = items.product_id
WHERE items.product_id IS NULL;
Table descriptions:
PRODUCTS
+------------------------+------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------------+------------------+------+-----+---------
+----------------+
| product_id | int(10) unsigned | NO | PRI | |
auto_increment |
ITEMS:
+------------------+------------------+------+-----+---------
+----------------+
| Field | Type | Null | Key | Default |
Extra |
+------------------+------------------+------+-----+---------
+----------------+
| item_id | int(10) unsigned | NO | PRI | |
auto_increment |
| product_id | varchar(45) | NO | MUL | 0
| |
Regards,
Harrison
--
Harrison C. Fisk, Senior Support Engineer
MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/
packaged/cluster.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
