MySql Temp Tables VS Views VS php arrays

Question

Hey Guys

I have currently created a facebook like page that pulls notifications from different tables, lets say about 8 tables. Each table has a different structure with different columns, so the first thing that comes to mind is that I'll have a global table, like a table of contents, and refresh it with every new hit. I know inserts are resource intensive, but I was hoping that since it is a static table, I'd only add maybe one new record every 100 visitors, so I thought "MAYBE" I could get away with this, but I was wrong. I managed to get deadlocks from just three people hammering the website.

So anyways, now I have to redo it using a different method. Initially I was going to do views, but I have an issue with views. The selected table will have to contain the id of a user. Here is an example of a select statement from php:

$get_events = "

    SELECT id, " . $userId . ", 'admin_events', 0, event_start_time
        FROM admin_events
        WHERE CURDATE() < event_start_time AND 
              NOT EXISTS(SELECT id
                         FROM admin_event_registrations
                         WHERE user_id = " . $userId . " AND admin_events.id = event_id) AND
              NOT EXISTS(SELECT id
                         FROM admin_event_declines
                         WHERE user_id = " . $userId . " AND admin_events.id = event_id) AND
              event_capacity > (SELECT COUNT(*) FROM admin_event_registrations WHERE event_id = admin_events.id)
           LIMIT 1

Sorry about the messiness. In any event, as you can see, I need to return the user Id from the page as a selected column from the table. I could not figure out how to do it with views so I don't think views are the way that I will be heading because there's a lot more of these types of queries. I come from an MSSQL background, and I love stored procedures, so if there are stored procedures for MYSQL, that would be excellent.

Next I started thinking about temp tables. The table will be in memory, the table will be probably 150 rows max, and there will be no deadlocks. Is it still very expensive to do inserts on a temp table? Will I end up crashing the server? Right now we have maybe 100 users per day, but I want to try to be future proof when we get more users.

After a long thought, I figured that the only way is the user php and get all the results as an array. The problem is that I'd get something like:

$my_array[0]["date_created"] = <current_date>

The problem with the above is that I have to sort by date_created, but this is a multi dimensional array.

Anyways, to pull 150 to 200 MAX records from a database, which approach would you take? Temp Table, View, or php?

Answer 1

The problem is that you are using correlated subqueries. I imagine that your query takes a little while to run if it's not in the query cache? That's what would be causing your table to lock and causing contention.

Switching the table type to InnoDB would help, but your core problem is your query.

150 to 200 records is a very amount. MySQL does support stored procedures, but this isn't something you would need it for. Inserts are not resource intensive, but a lot of them at once, or in sequence (use bulk insert syntax) can cause issues.

Answer 2

Some thoughts:

Temp Tables: temporary tables will only last as long as the session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing.

Views: These are mainly for hiding complexity in that you create it with a join and then access it like a single table. The underlining code is a SELECT statement.

PHP Array: A bit more cumbersome than SQL to get data from. However, PHP does have some functions to make life easier but no real query language.

Stored Procedures: There are stored procedures in MySQL - see: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

My Recommendation: First, re-write your query using the MySQL Query Analyzer: http://www.mysql.com/products/enterprise/monitor-test-drive.html

Now I would use PDO to put my values into an array using PHP. This will still leaves the initial heavy lifting to the DB Engine and keeps you from making multiple calls to the DB Server.

Answer 3

Try this:

SELECT id, " . $userId . ", 'admin_events', 0, event_start_time
    FROM admin_events AS ae
    LEFT JOIN admin_event_registrations AS aer
    ON ae.id = aer.event_id
    LEFT JOIN admin_event_declines AS aed
    ON ae.id = aed.event_id
    WHERE aed.user_id = ". $userid ."
    AND aer.user_id = ". $userid ." 
    AND aed.id IS NULL
    AND aer.id IS NULL
    AND CURDATE() < ae.event_start_time
    AND ae.event_capacity > (    
        SELECT SUM(IF(aer2.event_id IS NOT NULL, 1, 0))  
        FROM admin_event_registrations aer2
        JOIN admin_events AS ae2
        ON aer2.event_id = ae2.id
        WHERE aer2.user_id = ". $userid .")
    LIMIT 1

It still has a subquery, but you will find that it is much faster than the other options given. MySQL can join tables easily (they should all be of the same table type though). Also, the last count statement won't respond the way you want it to with null results unless you handle null values. This can all be done in a flash, and with the join statements it should reduce your overall query time significantly.