Cache results

[mmj]

Hi there,

I'm wanting to improve efficiency of my mySQL based script and what I want to be able to do is extract the top 20 rows from a table into a separate, smaller table to enable me to access this smaller table more quickly than the larger table.

I'd update it manually, as the 20 rows I need most will only change when I update the site. What is the easiest way to create a new table based on a result set?

[mmj]

I've just found out how to do it:


INSERT INTO tablename SELECT ...

This causes the results from the following SELECT statement to be inserted into a table.

[Karl]

It's a shame mySQL doesn't support views as they are ideal for something like this.

For anyone that doesn't know, a view in a database is just the results of a query presented like another table in the database, the database updates the view every time the tables it is made up of are changed. It's a pretty nifty feature.

[mmj]

That would be nifty.

Another way of doing what I particularly wanted in SQL databases other than mysql would be using nested select queries (which mySQL does not support, I believe).