OH MY GOD IT WORKS!!!!!!! I got 32 rows in set (0.08 sec). That is fantastic!!!!! Thank you so much!!!!
Now, when I do an explain on this query, I get the following: +-------+--------+---------------+---------+ | table | type | possible_keys | key | +-------+--------+---------------+---------+ | l | ALL | NULL | NULL | | p | eq_ref | PRIMARY | PRIMARY | | c | eq_ref | PRIMARY | PRIMARY | | a | eq_ref | PRIMARY | PRIMARY | | o | eq_ref | PRIMARY | PRIMARY | +-------+--------+---------------+---------+ +---------+---------------+------+---------------------------------+ | key_len | ref | rows | Extra | +---------+---------------+------+---------------------------------+ | NULL | NULL | 2647 | Using temporary; Using filesort | | 8 | l.PublisherID | 1 | Using where | | 8 | l.ComposerID | 1 | Using where | | 8 | l.ArrangerID | 1 | Using where | | 4 | l.CategoryID | 1 | | +---------+---------------+------+---------------------------------+ This seems really efficient, since the only large number of rows to search against is the main listings table, if I read this right. Is there any further optimization that I can do, or this as good as it gets? Believe me, I am NOT complaining!!! Thanks again! -Erich- > If every record in the listing table will have a corresponding record in > > the category table you may just include the category clause in with the > rest. > > WHERE > (a.ArrangerLname like '%$Criteria%' or > p.PublisherName like '%$Criteria%' or > c.ComposerLname like '%$Criteria%' or > l.Title like '%$Criteria%' or > l.CatalogNumber like '%$Criteria%') > AND > l.PublisherID=p.PublisherID and > l.ComposerID=c.ComposerID and > l.ArrangerID=a.ArrangerID and > l.CategoryID=o.CategoryID > > > > > Hi Evelyn, > > How would I do that - would something like this be what you had in > > mind? > > left join categories o on o.CategoryID = l.CategoryID > > > This goes in the WHERE clause, right? > > Thanks! > > -Erich- > > > -----Original Message----- > From: Schwartz, Evelyn [mailto:[EMAIL PROTECTED] > Sent: Friday, February 06, 2004 8:53 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Help with query > > You will need parentheses around the 'or' clauses of your where > > clause. > > You also don't seem to join the categories table with any other > > tables. If > you don't join tables you will create what is called a 'cross product' > query. If table A has 10 rows and table B has 20 rows then querying A > and B > will return 200 rows (every row of A will be joined with every row of > B!). -----Original Message----- From: Erich Beyrent [mailto:[EMAIL PROTECTED] Sent: Fri 2/6/2004 8:46 AM To: [EMAIL PROTECTED] Cc: Subject: Help with query Hi everyone, I am having a rough time with a query, which seems to be taking so long it hangs the systems. SELECT l.CatalogNumber, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID ORDER BY o.Alias"; How can I rewrite this query to be efficient (and functioning!) - I am fairly new to MySQL and could use lots of advice! Thanks! -Erich- -- 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]
