Not long ago, some highly knowledgeable people here kindly helped me
out with a fairly complex query...
Finding names of people (and other info) where one or more fields
match the search string in up to five tables (abstracting somewhat):
--------
select distinct
id, firstname, lastname, etc...
from
master_info as r
inner join general_info as g
left join table_1 as t1 on t1.id = r.id
left join table_2 as t2 on t2.id = r.id
left join table_3 as t3 on t3.id = r.id
left join table_4 as t4 on t4.id = r.id
left join table_5 as t5 on t5.id = r.id
where
g.id = r.id and
(t1.blurb like '%searchterm%' or t2.blurb like '%searchterm%' or
t3.blurb like '%searchterm%' or t4.blurb like '%searchterm%' or
t5.blurb like '%searchterm%')
--------
That's all fine and dandy, but now I need to extend this to a further
four tables... except it's really eight tables in four pairs. I'll
call these table_a and table_ga .. table_d and table_gd.
So far, I can get it to work if I add just one pair, in either of two ways:
left join table_ga as tga on tga.id = r.id
left join table_a as ta on ta.ida = tga.ida
or:
left join (table_ga as tga inner join table_a as ta) on
(tga.id = r.id and ta.ida = tga.ida)
in each case adding:
or ta.blurb like '%searchterm%'
to the where clause. As you'll realise this is because the text has
to match the blurb column in ta, which is in turn identified by its
own id which has to be matched in tga, which is simply two columns of
ids (one of people, one of blurbs).
That does, as I say, work, but it does slow things down pretty
drastically - from less than half a second to about four seconds
(whichever of the two methods I use). And when I add a second pair
(table_b and table_gb) it's nearly a minute, so obviously this is
going to multiply up very nastily if I add the other two pairs.
I've now added full text indices to the blurb columns in table_a and
table_b and that's speeded things up a lot - about 7.5 seconds now.
However, in this instance there are matches in both table_a and
table_b (as well as in some of the 1..5 tables). When I add the
remaining two pairs in - where I know there are no matches - well,
it's still running after several minutes, and that's after full text
indexing those tables too.
Obviously, there's a better way of doing this - any ideas?
(And I'm now cancelling the last query which still hasn't finished!)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
Without music to decorate it, time is just a bunch of boring
production deadlines or dates by which bills must be paid.
-- Frank Zappa
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
