Hi Jorge!

I'd say that if you ask a question that results in an answer of 10 million rows, you cannot be disappointed if it takes a few minutes to return the result! I've no experience with tables with billions of rows, but on those rare occasions when I write a query that return a dataset of a million rows or so, the result is normally going to be used for input into a statistical package and users don't mind having to wait a while (moreover, sorting is then not all that important).

One thing that I've never tried is:

with recursive ds_temp (name) as
(select min(ds.name)
FROM data d
join data_set ds on d.data_set_id = ds.id
join users u on d.user_id = u.id
WHERE d.user_id=XXX
union all
select min(ds.name)
FROM data d
join data_set ds on d.data_set_id = ds.id
join users u on d.user_id = u.id
join ds_temp temp on ds.name > temp.name
WHERE d.user_id=XXX
)
SELECT u.name, temp.name, d.data
FROM ds_temp temp
left join data_set ds on temp.name = ds.name
join data d on d.data_set_id = ds.id
join users u on d.user_id = u.id
WHERE d.user_id=XXX

I don't know whether this will be quick or slow, and neither whether it will return the dataset sorted on the data_set.name or not (I've just got a hunch that it might).

By the way, unless Firebird has changed in the last few versions, I recommend doing:
DROP INDEX data_user_id_idx;

The reason being that since the user_id field is also the first part of your primary key, that index should be sufficient. I tend to have only one field as a primary key in my tables (i.e. if I'd designed the table, I'd have another ID column, though that's probably more of a habit than something beneficial by itself, and with such a scheme your data_use_id_idx would be required).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Jorge
Sent: 3. november 2009 17:07
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Is there something like an Indexed View or indexed Computed Column?

Svein Erling Tysvær wrote:

> Seeing the query, view and index definitions (so that we know which
> fields are involved) and the plan normally helps quite a bit. Also, it
> would be good to know which version of Firebird you're using and how
> selective the indexes in the fields are. And how many rows does your
> query return?

I put this up on stackoverflow, I hope it's OK if I link over there.
That shows the schema example.

http://stackoverflow.com/questions/1666962/speeding-up-sql-query-when-sorting-on-foreign-keys

The PLAN for that SELECT query looks like this:

PLAN SORT (JOIN (USERS INDEX (RDB$PRIMARY1), DATA INDEX
(DATA_USER_ID_IDX, RDB$PRIMARY3), DATA_SET INDEX (RDB$PRIMARY2)))

The main big table "data" has about 1.2 billion rows and the "data_set"
table has about 800 million rows. I was wrong about the result set size
in my stackoverflow post, the result set varies but is usually around 10
million rows (it can be more but not on average).

If I run the query without ORDER BY then it starts returning results
quickly. When I add the ORDER BY Firebird starts filling the temporary
filesystem with a huge amount of data and it takes forever to run.

Thanks


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links