1

I need to perform query, save the result and re-use it multiple times in one session (something like WITH in Oracle DB).

This is the query I want to save:

SELECT `event`.id, event.name
FROM attendant
INNER JOIN event ON attendant.fk_event = event.id
WHERE attendant.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM organizer
INNER JOIN event ON organizer.fk_event = event.id
WHERE organizer.fk_user = 1

UNION

SELECT `event`.id, event.name
FROM invitation
INNER JOIN event ON invitation.fk_event = event.id
WHERE invitation.fk_user_to = 1

And then perform multiple queries like

SELECT id FROM savedquery WHERE .....

UNION

SELECT id FROM savedquery WHERE .....;

I've found TEMPORARY TABLEs but I don't think that I could use it since I need to perform this query for multiple different users at the time they run the request for events.

VIEW is also named, so I suppose there is the same problem.

What's the best solution to do this please?

  • 1
    Ok, I delete my answer, so I put as comment the query about get faster: SELECT e.id, e.name FROM event e WHERE EXISTS( SELECT 'ATTENDANT' FROM attendant a WHERE a.fk_event = e.id AND a.fk_user = 1 ) OR EXISTS( SELECT 'ORGANIZER' FROM organizer o WHERE o.fk_event = e.id AND o.fk_user = 1 ) OR EXISTS( SELECT 'INVITATION' FROM invitation i WHERE i.fk_event = e.id AND i.fk_user = 1 ) – Joe Taras Oct 7 '14 at 8:40
1

As you say, temporary tables are only visible within the session, and materialized views are not available in MySQL. There are several options (including the ones you have mentioned). I will name your original query as SELECT ...:

  • The WITH Oracle keyword is equivalent to CREATE TEMPORARY TABLE:

    CREATE TEMPORARY TABLE savedquery AS SELECT ...; -- you execute this for every connection
    SELECT id FROM savedquery WHERE .....
    UNION
    SELECT id FROM savedquery WHERE .....;
    

    You will have to create it for every connection.

  • A VIEW is shared among connections, but it is not an actual table- it can be executed in two ways, by creating a temporary table at the beginning of the execution or by executing the corresponding subquery:

    CREATE VIEW savedquery 
    ALGORITHM = TEMPTABLE  -- you probably want to use temptable algorithm
    AS SELECT ...;         -- you execute this only once
    
    SELECT id FROM savedquery WHERE .....
    UNION
    SELECT id FROM savedquery WHERE .....;
    

    If you were using 5.6, the subquery algorithm may be better, but this is without knowing the actual query.

  • As materialized views are not an option, you can emulate them by creating a real table, that will be shared among connections:

    CREATE TABLE savedquery AS SELECT ...;
    SELECT id FROM savedquery WHERE .....
    UNION
    SELECT id FROM savedquery WHERE .....;
    

    The problem with this is that it is not automatically updated (you have to do that with triggers or any other way); the positive thing is that you can add indexes (unlike views).

The problem is that your original query and your derived query are probably not going to be very efficient, and you probably could modify it to perform better, but that is another question...

  • So I'm gonna go with temp tables and recreate them for every connection. About efficiency of my query - I've had no idea that it could be performed better. Could you please tell me more in chat room? chat.stackexchange.com/rooms/info/17675/… – Jakub Turcovsky Oct 7 '14 at 8:46
  • 1
    @2rec you can use MyISAM or MEMORY ENGINEs to avoid transactional overhead, is up to you. Sorry, not available right now. – jynus Oct 7 '14 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.