Chris,
If you keep it in 5 different tables, the search will be as slow
as molasses in January because of the joins. I'd recommend using FullText
search on the text field. You *may* be able to do a Merge table on the 5
tables so MySQL sees it as 1 table. I'm not sure which of these features
are available in 3.23 so you'll have to check the docs. If these features
are not in 3.23 then I'd insist on an upgrade to v5.x. Either that or limit
each table to about 10 rows.<g>
The other alternative that just occurred to me is to do 5 separate
searches, each on only 1 table using a full text index. Then join the
results. That could be faster than what you're doing now.
Mike
At 09:00 AM 4/11/2006, you wrote:
First I should say I'm using MySQL 3.23.x because that's what's currently
available on our host's server. An upgrade to 5.x is promised any time
now, but I'm not holding my breath! So, with that in mind...
I'm trying to do quite a sophisticated search across several tables and am
running into trouble, maybe because of trying to use 'or', maybe because
of records not present, I dunno. Whatever it is, there's obviously a flaw
in my logic that I can't find.
Simplifying it to the relevant bits, I'm searching six tables of data
relating to people. For the present purpose I'll call the tables T1..T6.
T1 contains a record for each person, including lots of basic information
and an ID field that the other tables refer to. Tables T2 to T6 each have
three fields: that same id, a field for a two-letter language code (en,
fr, de, etc.) and a lump of text. The important thing is that in T2 to T6,
any one person might have several rows if they've provided information in
several languages - or none at all (relatively few have any data in T6,
for example).
I want to be able to enter a bit of text in the search form and find
anyone in the db with the search term anywhere in T2..T6, in any language.
My first instinct was this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
(t2.text like '%search_term%' and t2.id = t1.id)
or
(t3.text like '%search_term%' and t3.id = t1.id)
or
(t4.text like '%search_term%' and t4.id = t1.id)
or
(t5.text like '%search_term%' and t5.id = t1.id)
or
(t6.text like '%search_term%' and t6.id = t1.id)
However, that sent the system into what was obviously some huge loop
which, if I waited long enough, would have produced thousands upon
thousands of results. It also sent my UPS into overload, so I interrupted
it! The logic looks right to me, but obviously there's something wrong: as
a friend agreed, 'or' often doesn't seem to do what you expect in SQL!
So then I tried this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
t2.id = t1.id and t3.id = t1.id and t4.id = t1.id and t5.id = t1.id
and t6.id = t1.id and
(t2.text like '%search_term%' or t3.text like '%search_term%' or
t4.text like '%search_term%' or t5.text like '%search_term%' or t6.text
like '%search_term%')
That produced no results at all, and I quickly saw why: if a person has no
rows at all in any of T2..T6 they'll be excluded. Once I removed T6 from
the search, one person reappeared. She didn't have the search term in all
the other tables, but she does at least have entries there, but none in T6.
The first solution definitely seems more logical and elegant, but where
have I gone wrong, O MySQL gurus? I've investigated left joins (which I've
so far had no use for, strangely enough) but can't see how to apply them
in this case. Maybe subqueries, which I know we'll get in MySQL 5, are the
answer?
A workaround would be to force a row for every person in each of the
multi-lingual text tables - for English, say - even if the text field is
empty, but I don't like introducing redundant non-data like that. At least
the second search would work though.
Another possibility would be to use several queries, but that would mean
some fairly hefty rewriting of the php that puts this stuff together, so
I'd prefer to avoid it if possible. Surely it /must/ be possible in one query?
(Before we decided to make this thing multi-lingual it was simple of
course, because the five lumps of text were all in T1.)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
I used to think I was indecisive, but now I'm not so sure.
--
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]
