List:General Discussion« Previous MessageNext Message »
From:Velen Date:May 7 2008 6:39pm
Subject:Re: Any better ways that LEFT JOIN?
View as plain text  
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" <support@stripped>
To: <mysql@stripped>
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/mysql?unsub=1
>
>

Thread
Any better ways that LEFT JOIN?sbrattla7 May
  • Re: Any better ways that LEFT JOIN?Velen7 May