Google Answers Logo
View Question
Q: Complex queries in MySQL ( Answered,   2 Comments )
Subject: Complex queries in MySQL
Category: Computers
Asked by: james_fryer-ga
List Price: $40.00
Posted: 16 Jul 2003 01:53 PDT
Expires: 15 Aug 2003 01:53 PDT
Question ID: 231545
I'm a bit of an SQL newbie, having mostly written C etc., working on
first database application (using MySQL 4.0).

My application centres around events, which have a type and many

My database structure looks like this (fixed font will help):

 id      id         event_id          id
 name    type_id    recipient_id      name
I've simplified the structure and abstracted the application area
which makes the example contrived.

OK, so to find events matching a type named 'foo' I do:

    FROM Event e, Type t

Similarly to find events with a recipient 'bar':

    FROM Event e, Event_Recipient er, Recipient r

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
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
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

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:

FROM   Event e
   e.type_id IN (
     FROM   Type t
     WHERE = 'foo')
   OR IN (
     SELECT er.event_id
     FROM Event_Recipient er, Recipient r
     WHERE AND'bar')

FROM   Event e
     SELECT *
     FROM   Type t
     WHERE = 'foo' AND = e.type_id)
     SELECT er.event_id
     FROM Event_Recipient er, Recipient r
     WHERE AND'bar' AND er.event_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.
Subject: Re: Complex queries in MySQL
Answered By: alexander-ga on 24 Jul 2003 14:55 PDT
Ah, got it. You can use a temporary table.

                              FROM Event e, Type t 

                   FROM Event e, Event_Recipient er, Recipient r 
                  WHERE AND 



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

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. :)
Subject: Re: Complex queries in MySQL
From: stephenvakil-ga on 17 Jul 2003 09:49 PDT
MySQL 4.0 does not support subqueries.
Subject: Re: Complex queries in MySQL
From: stephenvakil-ga on 17 Jul 2003 09:50 PDT
Sorry, ignore.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy