I have a very complex query that basically gives me a master table of distinct combinations of various columns from multiple tables in my database, call it $query (using PHP), the $result = the result of the mysql_query. I'd like to be able to reuse that $result in subsequent smaller queries without having to perform the really complex query again.
Something like SELECT DISTINCT column from $result WHERE xxx AND xxx AND xxx. It works find if I actually put in the string for $result in, but it seems like it could be more efficient if it did not have to perform the whole complex query overtime. Is that possible ?
You can either:
Save your results into a temporary table:
CREATE TEMPORARY TABLE myTempTable SELECT ...
Then use that:
SELECT DISTINCT column FROM myTempTable WHERE xxx AND xxx AND xxx
Use a view:
CREATE VIEW myView SELECT ...
Then use that:
SELECT DISTINCT column FROM myView WHERE xxx AND xxx AND xxx
The difference is that views are visible across all sessions and will persist until manually dropped; whereas temporary tables are only visible within the session that created them (and therefore cease to exist when that session ends).
Also, views are exactly that: a view of the underlying data—thus when the underlying tables are updated, such changes will immediately be reflected when querying the view (except for normal transaction isolation behaviours). By contrast, a temporary table (just like a normal table) holds a static copy of the data that will only be changed when explicitly updated by subsequent queries upon it.
Obviously there is therefore a performance difference: views can use the indexes of the underlying tables, whereas temporary tables must have their own defined. But temporary tables use cached data and therefore do not have to rerun the underlying query each time (whereas views do).