I have a series of mysql stored procedures that query thousands of
records over 60 tables. these are quite slow and user has to wait 10
or 15minutes for results (I use a JSP to run the query and diusplay
data in html)

I can run these stored procedures out-of-hours (via cron) so as to
avoid the long wait......but where could I temporarily store the data
so that I can query/retrieve it very quickly the next day?

Do I need to create permemant tables that hold the output of the
stored procedures? (this is big overhead that I'd like to avoid)

So basically I need to be able to dump the data on a nightly basis (or
even hourly) and then have it instantly when I query it in a secondary/
temporary location

any ideas????

On 3/16/2011 8:19 AM, Doug Miller wrote:
Doug, are you referring to database normalization and relational theory?
There is a discussion of this at
http://dev.mysql.com/tech-resources/...alization.html
where the author refers to the "spreadsheet syndrome." Same thing?

Bill B

In article <f7157fe4-6baa-47cf-ab78-6826a3e75980 (AT) fe9g2000vbb (DOT) googlegroups.com>, "terry433iid (AT) yahoo (DOT) com" <terry433iid (AT) googlemail (DOT) com> wrote:
Fix your broken database design. It appears that the database has been
designed to support storing data, but not to support retrieving it. That's
backward. Design it so it supports the most commonly run queries, efficiently.

On 3/16/2011 8:52 AM, Doug Miller wrote:
Any references you find most useful for understanding this?

On Mar 16, 10:57*am, "terry433... (AT) yahoo (DOT) com"
<terry433... (AT) googlemail (DOT) com> wrote:
indexes, queries) to see whether you are actually doing this
correctly. I recently designed a small (4 table) database. In order to
trace a bug I had to perform a complicated JOIN which was not part of
the normal operation. This query was taking minutes. I added 2 indexes
and the query returned in a fraction of a second.

temporary one? More to the point, the life of a temporary table is the
life of the connection, so they won't do you any good.

In article <ilq6r9$b9g$1 (AT) news (DOT) eternal-september.org>, Bill B <me (AT) privacy (DOT) net> wrote:
No. Well, not entirely.

Normalization is part of proper database design, but it's quite possible to
construct a database that's fully normalized and still doesn't support
efficient performance of the queries that are most frequently run against it.

Not the same thing.

On 3/16/2011 10:13 AM, Doug Miller wrote:
Thank you, Doug, much appreciated.

Bill B

On Mar 16, 12:52*pm, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
To expand on this, data loaded into "data warehouses" is often "de-
normalised" in order to increase the efficiency of queries.

In article <ilqau5$qh2$1 (AT) news (DOT) eternal-september.org>, Bill B <me (AT) privacy (DOT) net> wrote:
Any book or tutorial on database design would be a good starting point.

El 16/03/2011 11:57, terry433iid (AT) yahoo (DOT) com escribió/wrote:
I haven't used temporary tables in MySQL so I cannot give you the exact
details for this DBMS. But the purpose of temporary tables is to store
data that:

- Is private to current session
- Will be deleted automatically when session ends

However, you need to store data that is common to all sessions and needs
to persist from one hour to one day. That's the very opposite

Said that, I don't see anything intrinsically wrong in using *regular*
tables to save this pre-calculated data. However, I'd first make sure
that there aren't other better solutions because it can be pretty tricky
to get it right:

- You need to ensure that data is not obsolete.
- You need to ensure that clients don't get partial data while the
update process is running.

Some ideas:

- Analyse your queries with the EXPLAIN command. Make sure you have the
appropriate indexes.

- Look for optimizations. An INNER JOIN (if appropriate) may be faster
than a LEFT JOIN. Remove unnecessary sorting. Watch functions like
GROUP_CONCAT() which can be very inefficient.

- Is your data strongly related to dates? Certain pieces of data, such
as daily sales, can benefit from partitioning.

- If data doesn't change often, enable MySQL's built-in query cache and
start your queries with SELECT SQL_CACHE. You can even launch a nightly
process that runs the query with the sole purpose of having it cached.


(BTW, Oracle has a beautiful feature that's exactly what you need:
materialized views. It's like a regular view, except that its data is
saved together with the query and you can control how to keep it updated.)


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--