I'm a bit of an SQL newbie, having mostly written C etc., working on
my
first database application (using MySQL 4.0).
My application centres around events, which have a type and many
recipients.
My database structure looks like this (fixed font will help):
Type---<Event---<Event_Recipient>---Recipient
id id event_id id
name type_id recipient_id name
I've simplified the structure and abstracted the application area
away,
which makes the example contrived.
OK, so to find events matching a type named 'foo' I do:
SELECT e.id
FROM Event e, Type t
WHERE e.type_id=t.id
AND t.name='foo'
Similarly to find events with a recipient 'bar':
SELECT e.id
FROM Event e, Event_Recipient er, Recipient r
WHERE e.id=er.event_id AND t.id=er.recipient_id
AND r.name='bar'
My application has a front end where users can opt to search in event
type, recipient etc. and combine these with AND, OR or NOT. So a user
could search for
'foo' in Type AND 'bar' in Recipient
which is straightforward, I translate into SQL which combines the two
queries above.
The OR case is much harder. I can't find a single SQL query which
corresponds to
'foo' in Type OR 'bar' in Recipient
If I combine my queries with OR, the SQL spins for a long time (these
are very big tables). I think I can see why -- there are two joins
involved and they are independent of each other, unlike the AND query,
and they are permuting.
I have tried UNION and this works but makes other things (like
sorting)
more difficult. Also it greatly complicates the function that is
generating the SQL.
I have looked at using an explicit JOIN (or maybe LEFT JOIN) but this
is
where my understanding gives out. I have had subselects suggested but
they are not supported in MySQL 4.0.
If anyone can tell me where I am going wrong then I would much
appreciate it. |
Request for Question Clarification by
alexander-ga
on
16 Jul 2003 03:30 PDT
I really think you want to use UNIONs here. Keep in mind that you can
do "(SELECT ...) UNION (SELECT ...) ORDER BY ...", so sorting need not
be complicated. Also, don't be afraid of programmatically generating
humongous queries.
I get the feeling that you're trying to misuse JOINs here, in search
of the magic pixie dust. You could technically do what you want by
starting with Event, LEFT JOINing the other tables on, and then
WHEREing out rows without matches, but this still triggers a full
table scan on Event, which means it's still slow, though not O^2 slow
like what you were trying to do. At any rate, if you think generating
the appropriate UNIONs is tricky, doing this is even more
mind-bending.
Could I offer you assistance in programmatically generating the
appropriate UNIONs?
|
Clarification of Question by
james_fryer-ga
on
16 Jul 2003 03:53 PDT
My problems with UNION are:
1. Sorting is a problem because I have to include the sorted fields in
the result set. For speed I am only returning event ID from the query.
But with UNION I can't use ORDER BY on another field unless it is named
in the SELECT.
2. If a query starts with a parenthesis, I can't use it in a CREATE
TABLE or INSERT statement. So those '(SELECT ...) UNION (SELECT ...)'
statements don't work in that context.
3. Query generation is more complex because when I come across an OR in
my user query I have to generate a new SELECT statement. I've done it,
it uses recursion. I'd prefer to make it simpler if I could.
If I could solve (2) then I could proceed, although the sorting problem
will eventually rear its head. OTOH I might be able to persuade the
clients to upgrade to MySQL 4.1 in which case I could use subselects --
what is your opinion on that technique?
|
Request for Question Clarification by
alexander-ga
on
16 Jul 2003 20:32 PDT
Do you know how subselects were suggested to you? The only way I can
think of would be to start with all records and then whittle the
result set down, much like I mentioned with the LEFT JOINs. Presumably
this would also trigger a full table scan, though I don't have a 4.1
installation to test it on.
|
Clarification of Question by
james_fryer-ga
on
17 Jul 2003 08:29 PDT
Subselect queries:
[1]
SELECT e.id
FROM Event e
WHERE
e.type_id IN (
SELECT t.id
FROM Type t
WHERE t.name = 'foo')
OR
e.id IN (
SELECT er.event_id
FROM Event_Recipient er, Recipient r
WHERE t.id=er.recipient_id AND r.name='bar')
[2]
SELECT e.id
FROM Event e
WHERE
EXISTS (
SELECT *
FROM Type t
WHERE t.name = 'foo' AND t.id = e.type_id)
OR
EXISTS (
SELECT er.event_id
FROM Event_Recipient er, Recipient r
WHERE t.id=er.recipient_id AND r.name='bar' AND er.event_id = e.id)
|
Request for Question Clarification by
alexander-ga
on
18 Jul 2003 22:10 PDT
Hmm, yeah, that might work. Like I said, I don't have a 4.1 install to
test on, but my intuition says that with the proper indexes, that
should be fine.
|
Clarification of Question by
james_fryer-ga
on
19 Jul 2003 02:32 PDT
But you can't rewrite that without subselects (even for $40 :-)
|
Request for Question Clarification by
alexander-ga
on
19 Jul 2003 06:04 PDT
Believe me, I tried. :) If any of the other researchers has some
insight, they're free to jump in.
|
Ah, got it. You can use a temporary table.
CREATE TEMPORARY TABLE Temp SELECT e.id
FROM Event e, Type t
WHERE e.type_id=t.id
AND t.name='foo'
INSERT INTO Temp SELECT e.id
FROM Event e, Event_Recipient er, Recipient r
WHERE e.id=er.event_id AND t.id=er.recipient_id
AND r.name='bar'
...
SELECT DISTINCT id FROM Temp (ORDER BY ...)
Of course, you'll have to include in the first two SELECTs all the
fields you want to reference in the third, but you need not send them
to your application. Temporary tables are memory based (unless they
get too big, obviously), so performance shouldn't be an issue. In
fact, MySQL sometimes uses temporary tables internally for complex
queries.
Keep in mind that the columns of the temporary table are defined by
the SELECT you do in the CREATE TABLE statement; if you run into
problems with that, or even if it's just more convenient, you can
define the table first with a more usual CREATE TEMPORARY TABLE
statement, then just SELECT into it as necessary.
The DISTINCT in the final SELECT just makes sure that you don't get
returned multiple copies of the same id, even if it's matched in more
than one of the other SELECTs.
Temporary tables are private to the connection and are automatically
cleaned up after the connection closes, so you don't need to worry
about namespace conflicts or housekeeping in the event of an error. If
you need to do more than one such search in the same connection, just
do a "DROP TABLE Temp" after you're done with it, or give the second
table a different name.
It still isn't as simple as we all might like, but it does appear to
address your primary concerns. :) |
