Re: mySQL hits or Storable retrieves?

Hi Hero Zzyzzx,

There are many variables that go into answering your questions, but the one that sprang to my mind is: "does the flavor of SQL you are using support temp tables"? I know MySQL does not, but Informix for example does.

Temp tables are a great way to store data between queries inside SQL itself, which can be an ideal out-of-memory storage solution for many applications. By using temp tables properly, you can split up complicated queries into several simple queries and even perform queries, functions, and sorts that would be impossible in a single SQL statement with a given table layout.

So, that's my advice. Any time I hear about in-memory storage that could incur a hit and cron-tabbed scripts to pick up the slack, I take a look at the given tool set for more elegant methods. In the case of SQL, there are many, and formulating a query for an application can save you a lot of time on the front end with other data structures.

You could say my answer is off-topic to Perl, but in a way I believe it is "perlish" in philosophy:

The elegant and the appropriate, sufficiently understood and applied, have the power to transform the compound and confusing into the concise and beautiful.

-cyberscribe

Comment on Re: mySQL hits or Storable retrieves?

Replies are listed 'Best First'.
Re: Re: mySQL hits or Storable retrieves? by astaines (Curate) on Oct 03, 2001 at 15:43 UTC
Maybe I'm confused,(in fact I am confused), but maybe I'm specifically confused... MySQL has got TEMP tables, which are in memory, SQL accessible, databases. I've used these for small data warehouse style problems in the past. They're very fast. Is this what you mean?? -- Anthony Staines	[reply]
Re: Re: Re: mySQL hits or Storable retrieves? by Hero Zzyzzx (Curate) on Oct 03, 2001 at 17:35 UTC
The problem is that each "view" if you will, which is a combination of the users' accesslevel and category they are requesting, filtered down by the domain that the request is coming from, requires me to run multiple queries. My understanding was that mySQL had a memory-caching feature for regular tables, in addition to the RAM-only HEAP tables that your alluding to.These views could also get large down the road. (I'm migrating a Lotus Domino (yuck!) NSF that currently has 2,700 documents int it.) I guess I just thought that it'd be faster and more elegant to run the queries that create the data structures I need periodically, store them to disk with Storable and then retrieve them when I need them, rather then keep two different sets of SQL, one for selecting from a memory-only HEAP table and one for updating to the main database. It's actually far simpler for me to solve this problem using multiple SQL queries, too. I'll post example code soon. It's suprisingly (at least to me) simple! -Any sufficiently advanced technology is indistinguishable from doubletalk.	[reply]