I�m converting an old DBF aplication to MySql an I�m having a very strange problem.
When I first execute a query it takes from 12 to 30 seconds to have an answer, after
that it runs in about 2 or 3 seconds.
It�s a very large file (ZipCodes) and I must seek by ZipCode, by State/City/Street
(where the problem occurs) or by State/Street (not so important)
In a first step we are using MyODBC (the programs are still in Visual Fox) afterwards
they will be converted to another language.
the whole line is something like that
SELECT * FROM cepect USE INDEX (cepect1) WHERE state="SP" AND city="SAO PAULO " AND
LEFT(street,6) = "ESTELA" ORDER BY uf,cidade,logradouro
comments :
cepect ==> file name
cepect1 ==> index name (ordered by uf,cidade,logradouro)
we use LEFT because I need all streets with that begins whith that name
we had to put the order clause because it was retriving in another order (why the
output isn�t in the named index order ?)
Well that�s the problem..
Other considerations :
- I�m an old guy...
- I�m used to control my whole file (dbf, c-tree, and so on)
- I�m from the time that develloper�s had to knew what they wanted and mastered the
whole file
The old program used a grid and the pointer just skip to the first record that matched
the desired key, so the user could look around and choose the desired record (In that
case it�s really a good feature)
The question is don�t you have direct acess routines where I could :
- "find" or select a record passing a key (an old find/seek command)
- "skip" records (on both sides using the actual index of course)
- retrieve records these records
Or in one line :
- direct access and control to files for productivity programs
in another line :
- may I skip SQL processor...
If you can explain me the first problem I�ll be pleased, the job is for a courier and
this query is one of the main ones (and more used)
The other questions are just ..., well probably there are other developers that know
exactly with record must be updated and need speed...
Thanks a lot
---------------------------------------------------------------------
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
