mysql中的临时表问题

Question

I am working on a little reporting and think I need to use temp tables. My code performs a api call to get all the user cookies that interacting with that system for the day.

Now I have a table with all the cookies and the type of browser they use, I could just loop through the array of cookies provided by the api and do a sql look up for each and incriment counters for the diff browsers, so in the end I can report the number of browser access's done by different browsers.

This however as you can imagine could take very long if there are allot of cookies (which there are).

From what I know the solution would be to make a temp table of the cookies passed to me and innerjoin it to my cookie data table and then do a

select count(id),broswer ... group by browser

Below is a more code view of what I want to try and do:

$json = file_get_content($apiURL);
$cookies = '';
foreach($json as $val){$cookies .= ','.$val['cookie']; }
$cookies = substr($cookies,1);

mysql_query('CREATE TEMPORARY TABLE passedcookies (
  `cookie` varchar(200) NOT NULL,
  PRIMARY KEY(cookie)
) values ($cookies);')

mysql_query('SELECT count(cd.id), cd.browser FROM cookiedata cd
            INNER JOIN passedcookies pc
            ON pc.cookie = cd.cookie
            GROUP BY cd.browser;');

mysql_query('DESTROY TEMP TBL;');

Do I have to destroy the temp table? when is it destroyed? and if 3 users run the report will each get their own temp table?

Thanks for any help

Answer 1

You can destroy the temp table if you wish. If you don't, it will be destroyed automatically when your MySQL connection is closed. Unless you're using shared connections, this will happen when the PHP script exits.

If you're not using shared connections, then each user will get their own temp tables.

P.S. Your SQL syntax for creating the temporary table is wrong, but you didn't ask about that. There's no VALUES clause in CREATE TEMPORARY TABLE, that belongs in an INSERT statement that you use after it.

Actually, for what you're doing, I don't think you need a temporary table. The query:

SELECT browser, count(*)
FROM cookiedata
WHERE cookie in ($cookies)
GROUP BY browser

should work fine. I think MySQL implements IN (list) pretty well.