Hi, I would usually use something like : SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword K1, track T1, Keyword2 K2 where k1.keyword_trackid=t1.track_id and t1.track_id=k2.keyword_trackid where k1.keyword like '% <keyword> %'
and use index on all 3 tables on the trackid fields and keyword field. I could get my result with such query in 2 secs for about 1.5million records. another way round is to create tmp tables where : table A will contain info relevant to condition1 table B will contain info relevant to condition2 table C will contain info from original table and matching results in table A & B. This one may be slower. Regards, Velen ----- Original Message ----- From: "sbrattla" <[EMAIL PROTECTED]> To: <mysql@lists.mysql.com> Sent: Wednesday, May 07, 2008 6:41 PM Subject: Any better ways that LEFT JOIN? > > Hi, > > My scenario is as described below, and i am in search of a better way to run > a query for that scenario. Any bright ideas are more than welcome! > > I have a table <keyword> (which contains keyword_id, keyword_trackid, > keyword_title) and a table <track> (which contains track_id, track_title). > Obviously, i have a number of keywords describing each track. > > I need to search for a keyword, and then get all tracks which this keyword > describes. However, i also need all other keywords which describes the found > tracks. I currently solve this with the following query: > > SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword K1 > LEFT JOIN track T1 ON T1.track_id = K1.keyword_trackid > LEFT JOIN keyword K2 ON T1.track_id = K2.keyword_trackid > WHERE (K1.keyword_keyword LIKE '%<keyword>%')"; > > I am not really worried about full text search right now, as i am more > worried about that the query itself most likely will take ages when the > number of keywords gets bigger. > > I could probably also do this with programming, but then again i would have > to get the list of all keywords from the database...which could be something > like 5 million keyword - track relations. > > So, if you would happen to know how this query can be optimised, then i'd > very much appreciate that! > -- > View this message in context: http://www.nabble.com/Any-better-ways-that-LEFT-JOIN--tp17106190p17106190.ht ml > Sent from the MySQL - General mailing list archive at Nabble.com. > > > -- > 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]
