| Jacques Brignon 2006-01-31, 7:23 am |
|
----- Message transféré de Jacques Brignon <jacbrignon@online.fr> -----
Date_: Tue, 31 Jan 2006 13:52:16 +0100
De_: Jacques Brignon <jacbrignon@online.fr>
Adresse de retour_:Jacques Brignon <jacbrignon@online.fr>
Sujet_: Re: Trans.: Re: Finding the row number satisfying a conditon in a
result set
À_: SGreen@unimin.com
Thanks Shawn for the detailed answer,
What I am currently doing is basically what you propose, I do a full query to
retreive the row numbers of the subset I want to display and of the "selected"
record if any in that subset, then I use another query with LIMIT to get those
rows for display.
What I am trying to do is to improve the performance by limiting the number of
queries and by identifying the most efficient way of finding the row number of
the search record. I am currently using brute force by loopiong through the
result set until I find the record. The proposal of storing the set in a temp
table should improve that, allowing to retrieve the row by a query on that
table which we can expect to be faster.
So As you correctly describe, what I need is to allow the user to scroll through
the set, and as you correctly describe, I am therefore usiong your option 2
doing one query to locate the rows and one with limit to get those to be
displayed and of course I am hitting performance issues. I also noticed that
all the queries using limit do not run at the same speed, the more you get
close to the end of the data set the more it takes time.
I uderstand the approach number 3 using a temp table, I am also intersted in
your approach number 1 but I am not sure to understand what you mean and how
you do that using the PHP MySql function libray.
Do you mean passing all the rows of the result at once to the client application
and storing them in memory (an array)? If the result set is big, couldn't we hit
some limits or experience other performance issues? I see how to get in PHP the
values of one row of the result set, how do you get all the rows at once other
than looping through the result set and getting one row after the other?
--
Jacques Brignon
Selon SGreen@unimin.com:
> Jacques Brignon <jacbrignon@online.fr> wrote on 01/30/2006 10:18:59 AM:
>
> set
> rank
> identifier
> same
> the
> sorted in
> set,
> very
> a
> how
> number is
> results
> the page
> 563 in
> like
> 561 to
> set
> the
> the set
> out the
>
> Yes, that is much clearer. Assuming that your results ARE ordered by some
> criteria (such as by name) so that the sequence of one query execution
> closely resembles that of another then you can artificially create a
> sequence number by saving those results into a temporary table with an
> auto_increment column.
>
> CREATE TEMPORARY TABLE tmpResults (
> rownum int unsigned auto_increment
> , name varchar(50) not null,
> , ... other columns in your results ...
> , primary_key (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
> send them to the client as a form of index. Then the client can ask the
> server for the FULL query and use the LIMIT offsets you had from the
> "index" query.
>
> What it boils down to is this:
>
> a) What you have described may look user friendly but it is database
> intensive. Your application performance will probably suffer.
> b) Most queries only ask for what they actually need. If you only wanted
> results that match a certain name or other condition, only ask for those
> rows. That may mean modifying your client so that it only asks for the
> rows the user wants to see.
> c) If you want your user to "scroll through" a set of results you have
> 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
> separate result set. This can become highly intensive if what you actually
> wanted to show is in the 100th iteration of the query.
> 3) use a temporary (or static) table to cache and serialize your
> results. Use it to navigate to the subset of records you seek.
>
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
----- Fin du message transféré -----
--
Jacques Brignon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|