On 13-Jul-01 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)
Ok, looking at the fields:
Country(3) looks strange; ISO3166-1 has two letter codes, 3166-2, 5 letter.
IND is redundant and just eats space. better to drop it, and put
a PRIMARY key on (Cod,Country,ORD).
> Cod are indexed
(now taken care of by your new primary key)
> Descr are Indexed
Why ?
> ORD are Indexed
(You didn't say what this was; Might want to keep this index, depending
on your queries)
> The Table have 59324 Records actually and all were from Spain and then the
> clause where with field country has no effect.
>
The server probably considered using the primary key(IND), but because there
were too many 'ES' entries, it was just as fast to scan the whole table.
>
> When I execute "Select * from cpostal order by cod", the response takes
> about 3 or 4 seconds.
>
> It's very big time!
>
is that by wall time? what does the mysql client say :
select * from da_table order by Cod;
...
59324 rows in set (?.?? sec)
^^^^ ---------- ?
> 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?
>
Is this with MS-Access ?, that will be a huge chunk of your processing time.
i have no suggestions for this.
> Well it seems to be continued a worst explanation :(
>
> Sorry about that but my english are worst! :(
>
Clear enough to me, and way above my B&B (beer & babblefish) Espanol.
Regards,
--
Don Read [EMAIL PROTECTED]
-- 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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php