With a result set of 10 million rows, I'd say users should expect to wait a while. I'd recommend you to delete the DATA_USER_ID_IDX index since USER_ID also is the first field of the primary key for DATA.

Other than that, well, you could try:

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

and see whether this returned the dataset sorted or not (I suspect it does, but have never done anything similar) and whether the speed is acceptable or much worse than it is at present.

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