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
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.
----- Original Message -----
From: "sbrattla" <support@stripped>
Sent: Wednesday, May 07, 2008 6:41 PM
Subject: Any better ways that LEFT JOIN?
> My scenario is as described below, and i am in search of a better way to
> 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
> tracks. I currently solve this with the following query:
> SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword
> 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
> to get the list of all keywords from the database...which could be
> 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:
> 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/mysql?unsub=1