Can you see where the column "possible_keys" is NULL for every table? That means that there are NO (none, zilch, nada, zero) indexes that can be used to save your database engine from the trouble of doing a full table scan for EACH AND EVERY MATCH in your query. I don't even see where you defined any PRIMARY KEYS on any of your tables. (May I suggest you hit web and do a little homework on indexes and primary keys?)
Try adding these indexes then let me know how your query performs: alter table products add key(id), add key(sequential_id); alter table products_categories add key(prodid, plft, prgt), add key(prod_sequential_id); alter table products_masids add key(prodid), add key(prod_sequential_id); alter table products_media add key(prodid), add key(prod_sequential_id); Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Chris W. Parker" <[EMAIL PROTECTED]> wrote on 10/07/2004 04:09:22 PM: > [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> > on Thursday, October 07, 2004 12:30 PM said: > > > You set up your situation very well but for one small item. Please > > allow me to kindly introduce you to the EXPLAIN command.... > > > > http://dev.mysql.com/doc/mysql/en/EXPLAIN.html > > hey! that looks like it might come in useful. :) i didn't know about > this. > > > Post the results from using that on your query and we will be good to > > go :-) > > here you are: > > +-------+--------+---------------+------+---------+------+------+------- > --------------+ > | table | type | possible_keys | key | key_len | ref | rows | Extra > | > +-------+--------+---------------+------+---------+------+------+------- > --------------+ > | pmed | system | NULL | NULL | NULL | NULL | 0 | const > row not found | > | p | ALL | NULL | NULL | NULL | NULL | 733 | where > used | > | pc | ALL | NULL | NULL | NULL | NULL | 753 | where > used | > | pmas | ALL | NULL | NULL | NULL | NULL | 1410 | > | > +-------+--------+---------------+------+---------+------+------+------- > --------------+ > 4 rows in set (0.00 sec) > > > > thanks, > chris. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
