| SGreen@unimin.com 2006-01-30, 11:24 am |
| --=_alternative 00565C0D85257106_=
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
Jacques Brignon <jacbrignon@online.fr> wrote on 01/30/2006 10:18:59 AM:
> Oops! forgoten to include the list in the relply
>=20
> --
> Jacques Brignon
>=20
> ----- Message transf=E9r=E9 de Jacques Brignon <jacbrignon@online.fr> ---=
--
> Date : Mon, 30 Jan 2006 16:16:53 +0100
> De : Jacques Brignon <jacbrignon@online.fr>
> Adresse de retour :Jacques Brignon <jacbrignon@online.fr>
> Sujet : Re: Finding the row number satisfying a conditon in a result=20
set
> =C0 : Jake Peavy <djstunks@gmail.com>
>=20
> Selon Jake Peavy <djstunks@gmail.com>:
>=20
rank[color=darkred]
identifier[color=dar
kred]
same[color=darkred]
the[color=darkred]
sorted in[color=darkred]
set,[color=darkred]
very[color=darkred]
a[color=darkred]
>=20
> Thanks for the tip, I am going to think to it as I do not see right away =
how
> this solves the problem.
>=20
> I agree with your comment, This is precisely because the result row=20
number is
> not in the database that I need to find it.
>=20
> The problem I am trying to solve is the following:
>=20
> A query returns a result set with a number of rows, lets say 15000 as an
> example.
>=20
> I have an application wich displays those 10 by 10 with arrows=20
> based navigation
> capabilities (first page, previous page, next page, last page).
>=20
> I also have a search capability and I need to find in which set of 10=20
results
> the row I search for will be diplayed in order to show directly the=20
> appropriate
> page and to know what is the rank of this row in the result set or in=20
the page
> to show the searched result row "selected".
>=20
> As an example the row having a customer id of 125, would have the row #=20
563 in
> the result set (not orderd by customer id but by some other criterion=20
like
> name) and would therefore be displayed in the page showing result rows=20
561 to
> 570
>=20
> When I say row I do not mean a row in any table but a row in the result=20
set
> produced by the query which can touch several tables.
>=20
> None of the fields of the result set contains the row number, it is just =
the
> number of time I have to loop through the result set to get the row in=20
the set
> which matches my criterion.
>=20
> I hope this makes my question clearer.
>=20
> I am sure this is a pretty common problem, but I have not yet figured=20
out the
> clever way to tackle it!
>=20
> --
> Jacques Brignon
> ----- Fin du message transf=E9r=E9 -----
Yes, that is much clearer. Assuming that your results ARE ordered by some=20
criteria (such as by name) so that the sequence of one query execution=20
closely resembles that of another then you can artificially create a=20
sequence number by saving those results into a temporary table with an=20
auto=5Fincrement column.
CREATE TEMPORARY TABLE tmpResults (
rownum int unsigned auto=5Fincrement
, name varchar(50) not null,
, ... other columns in your results ...
, primary=5Fkey (rownum)
, key(name)
);
INSERT tmpResults (name,... other columns ...)
SELECT name, ... other columns ...
.... (rest of query) ...;
Now you have somewhere that has a row number on each row of your query. In =
most applications, it is more efficient to either send the whole recordset =
to the client and display the results in pages based on the cached results =
or to run a smaller query of just those fields you want to search by and=20
send them to the client as a form of index. Then the client can ask the=20
server for the FULL query and use the LIMIT offsets you had from the=20
"index" query.=20
What it boils down to is this:
a) What you have described may look user friendly but it is database=20
intensive. Your application performance will probably suffer.
b) Most queries only ask for what they actually need. If you only wanted=20
results that match a certain name or other condition, only ask for those=20
rows. That may mean modifying your client so that it only asks for the=20
rows the user wants to see.=20
c) If you want your user to "scroll through" a set of results you have=20
three simple options:
1) pull them all on the first query and navigate the results client-side =
(very fast, quite scalable, most flexible)
2) re-execute the query multiple times on the server and work with each=20
separate result set. This can become highly intensive if what you actually =
wanted to show is in the 100th iteration of the query.=20
3) use a temporary (or static) table to cache and serialize your=20
results. Use it to navigate to the subset of records you seek.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00565C0D85257106_=--
|