Hi Andy, Thanks for your comments and my apologies on the late reply.
To optimize my queries I restructured the tables - adding another table into the mix. Here is my table structure: CREATE TABLE kcs_thread_types ( id int(11) NOT NULL auto_increment, typeName varchar(255), PRIMARY KEY (id), UNIQUE typeName (typeName) ); CREATE TABLE kcs_threads ( id int(11) NOT NULL auto_increment, dateinserted timestamp(14), manufacturer varchar(255), type_index int(11), newUrlType varchar(255), colour varchar(255), colourID varchar(255), price decimal(8,2), image varchar(255), PRIMARY KEY (id) ); CREATE TABLE kcs_threadgroups ( id int(11) NOT NULL auto_increment, groupName varchar(255), groupNameUrl varchar(255), type_index int(11), thread_index varchar(255), PRIMARY KEY (id) ); The query I am now using is: SELECT * FROM kcs_threads as t1 LEFT JOIN kcs_threadgroups as t2 ON t1.type_index=t2.type_index LEFT JOIN kcs_category_threads as t3 ON t2.type_index=t3.id WHERE t1.manufacturer='DMC' ORDER BY t1.type,t2.groupName; When I do an explain on the query I get: +-------+--------+---------------+---------+---------+---------------+-- ----+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+---------------+---------+---------+---------------+-- ----+------------+ | t1 | ALL | NULL | NULL | NULL | NULL | 2067 | where used | | t2 | ALL | NULL | NULL | NULL | NULL | 286 | | | t3 | eq_ref | PRIMARY | PRIMARY | 4 | t2.type_index | 1 | | +-------+--------+---------------+---------+---------+---------------+-- ----+------------+ I am still getting the output I had before in the 77,000+ results being returned. I am at wits end here and don't know where else to look *shrugs* ANY clues? Do you want to see some of the table data? Thanks Aaron > -----Original Message----- > From: Andy Jackman [mailto:[EMAIL PROTECTED] > Sent: August 11, 2003 2:21 PM > To: Aaron Wolski > Cc: [EMAIL PROTECTED] > Subject: Re: Help debugging this Query? > > Aaron, > It sounds like the join has duplicates on both sides. If you join a->b > where a is unique (like a primary key) then you will get count(b) > records (where b is the rows that match a). Or if b is unique then you > will get count(a) records. However if neither a or b is unique you get > count(a) * count(b) records. Without you tabledefs it is difficult to > see if this is the case. > > Try this: > select count(*) as xx from kcs_threadgroups group by threadType having > xx > 1; > If you get a result then you have duplicates on threadType > > select count(*) as xx from kcs_threads where manufacturer='DMC' group by > type having xx > 1; > If you get a result then you have duplicates on Type for manufacturer > DMC. > > If you have dups for both then you are getting the result you are asking > for. > > If this doesn't help, please publish your tabledefs. It's ok to simplify > them so we don't have to wade through tons of stuff that has nothing to > do with the problem. > > Regards, > Andy. > > Aaron Wolski wrote: > > > > Hi Guys, > > > > I have 2 queries: > > > > select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE > > t2.manufacturer='DMC' AND t1.thread_index=t2.id ORDER BY t2.type, > > t1.groupName > > > > Using the explain command (explain select.) I get: > > > > +-------+--------+---------------+---------+---------+-----------------+ > > ------+---------------------------------+ > > | table | type | possible_keys | key | key_len | ref | > > rows | Extra | > > +-------+--------+---------------+---------+---------+-----------------+ > > ------+---------------------------------+ > > | t1 | ALL | NULL | NULL | NULL | NULL | > > 286 | Using temporary; Using filesort | > > | t2 | eq_ref | PRIMARY | PRIMARY | 4 | t1.thread_index | > > 1 | where used | > > +-------+--------+---------------+---------+---------+-----------------+ > > ------+---------------------------------+ > > > > > > > > select * FROM kcs_threadgroups AS t1, kcs_threads AS t2 WHERE > > t2.manufacturer='DMC' AND t1.threadType=t2.type ORDER BY t2.type, > > t1.groupName > > > > Using the explain command I get: > > > > +-------+------+---------------+------+---------+------+------+--------- > > ------------------------+ > > | table | type | possible_keys | key | key_len | ref | rows | Extra > > | > > +-------+------+---------------+------+---------+------+------+--------- > > ------------------------+ > > | t1 | ALL | NULL | NULL | NULL | NULL | 286 | Using > > temporary; Using filesort | > > | t2 | ALL | NULL | NULL | NULL | NULL | 2067 | where > > used | > > +-------+------+---------------+------+---------+------+------+--------- > > ------------------------+ > > > > > > With the second query, I am getting over 77,000 results returned and > > with the first query I am getting 2067 - the correct amount. > > > > The only differences between the 2 is that in the first I have WHERE > > clause t1.thread_index=t2.id and in the second I have > > t1.threadType=t2.type > > > > I need to have the WHERE like the second query but for the life of my I > > have no clue why it is returning over 77,000 results. > > > > Can anyone help guide me to get the results of #1 but with the WHERE of > > #2? > > > > Thanks so much! > > > > Aaron > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
