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
