List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:July 13 2001 6:41pm
Subject:Re: Dificult question.....
View as plain text  

LIMIT X,Y
Skips X rows and gives you the next Y.
Just increment and decrement X as necessary
between selects to scroll through table.

David Tomas Fargas wrote:

> Well, sorry about my worst explanation :)
> 
> The table is a Spain Postal Codes Table.
> It has fields: Cod(5),Descr(100),Country(3),IND(13),ORD(3).
> All are varchars.
> Because there are possible to have the same Postal Codes and diferent
> description or different country, The Primary Index is IND that it's a
> combinated unique field (country+"#"+cod+"#"+ord)
> Cod are indexed
> Descr are Indexed
> ORD are Indexed
> The Table have 59324 Records actually and all were from Spain and then the
> clause where with field country has no effect.
> 
> 
> When I execute "Select * from cpostal order by cod", the response takes
> about 3 or 4 seconds.
> 
> It's very big time!
> 
> Well. I need to create a window that maintains the postal codes and I need
> Cod Descr, and Country fields for that. The Window simple have a few
> buttons(First, Previous, Next, Last, New, Delete...etc.) That needs The
> complete big recordset or the simple little recordset with a limit clause.
> 
> But The form, can search a value from the totally table. I think i found it
> by where clauses but when I find it I need to maintain the functionality of
> those buttons, and then, if I didn't know the relative position in the
> sorted table, how can I present the previous or next record of the record I
> Found?
> 
> Well it seems to be continued a worst explanation :(
> 
> Sorry about that but my english are worst! :(
> 
> 
> 
> 
> Thanks a lot for your time.....
> 
> 
> 
> -----Mensaje original-----
> De: Don Read [mailto:dread@stripped]
> Enviado el: viernes, 13 de julio de 2001 18:14
> Para: David Tomas Fargas
> CC: mysql@stripped
> Asunto: RE: Dificult question.....
> 
> 
> 
> On 13-Jul-01 David Tom`s Fargas wrote:
> 
>> I Have a large table with 59.000 records and if i execute a simple select
>> since "Select * from table order by cod" it takes 4 seconds to process
>> query!
>> and if table will have 590.000 then... well, you see it.
>> 
>> It's possible to accelerate it, without where and limit clausules?
>> 
> 
> 
> do you have an index on cod ?
> 
> 
>> I think no, and in my find of solutions I develop an idea...
>> 
>> If I execute the same query with a where clausule with unique value, and
>> limit clausule to 1 value it results fine in an instant.
>> Ok I have one record, and I need to go to next or previous record...
>> 
>> And then...it's a big problem!
>> 
>> Because the query is sorted and, of course, I don't have any idea the
>> primary value for the next and previous record, How can I execute another
>> query to obtain those records?
>> 
>> I think I need a "imaginary value" in te record that gives me the position
>> of the current record in a totally table. Because the table are sorted,
> 
> the
> 
>> autonumeric fields can't help me.
>> 
> 
> 
>  sorted ?
> 
> 
>> And if i simple use a variable in my application to monitoring the
>> ficticious sorted position and play with the limit clause, I will can't
> 
> set
> 
>> big jumps fast between records  such a search tool ...
>> 
>> how can I get this "imaginary value"?
>> 
> 
> 
> You lost me.
> Please post describe table and a couple of example rows of the dataset
> 
> Regards,
> --
> Don Read                                       dread@stripped
> -- It's always darkest before the dawn. So if you are going to
>    steal the neighbor's newspaper, that's the time to do it.
> 
> 
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread79429@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


-- 
Gerald L. Clark
gerald_clark@stripped

Thread
Dificult question.....David Tomàs Fargas13 Jul
  • RE: Dificult question.....Don Read13 Jul
    • RE: Dificult question.....David Tomas Fargas13 Jul
      • RE: Dificult question.....Don Read13 Jul
      • Re: Dificult question.....Gerald Clark13 Jul
      • Re[2]: Dificult question.....Werner Stuerenburg13 Jul
RE: Dificult question.....David Tomàs Fargas13 Jul