On 23-Aug-2001 Filippo Galante wrote:
> 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.
Because it's got the index ( & maybe the result) in cache memory.
> 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 ?)
>
Index(es) are for finding records, not imposing order.
so drop the 'USE INDEX'.
MySQL is pretty good at choosing the proper one to use.
That LEFT clause is going to mess up the optimizer.
try ... AND street LIKE 'ESTELA%'.
Also put an index on state ( & mabe city).
> (why the output isnīt in the named index order ?)
>
SQL results have no order, you must specify it with an 'ORDER BY' clause.
> 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)
Same reason that (most) Perl hackers don't C, & C folks don't do assembler.
Once proven, a low-level implemtation is abstracted to a black-box.
> - Iīm from the time that develloperīs had to knew what they wanted and
> mastered the whole file
>
Then you musta been thrilled changing from dbm to ndbm.
> 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...
Not if you plan on using SQL.
>
> 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
>
There's always the source ...
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.
(53kr33t w0rdz: sql table query)
---------------------------------------------------------------------
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
