MySQL performance question

I am changing a thirdy party application from Paradox to MySQL using Zeos Components.
There are various TTable components and original application uses FindKey and FindNearest Dataset methods.
What If I change TTable to TZQuery component and FindKey/FindNearest to Locate?
Is Locate method equivalent to a SELECT with WHERE clause adapted to seach criteria indicated in loPartialKey?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
good for you !!!
FindKey/FindNearest is the slowest possible way of locating a record

Locate is not equivalent to Select/Where if you consider network traffic

Lets say you want 1 record out of 2 million records from 1 table:
with Locate:
Open table -> copy 2 million records from database to pc -> find record in memory
Depends on Memory, NetWork traffic

with Select/Where
Open query -> Database returns 1 record
(Depends on index for faster finding of records)

My guess to your speed gain will be x100 :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jclobocarAuthor Commented:
So, I have to rewrite all the code for selection using queries, correct?
Geert GOracle dbaCommented:
basically that would be the best idea

it's probably a poorly performing application anyway ?
(unless it's only running over max 500 records)
Geert GOracle dbaCommented:
you should try and avoid loading the whole table into memory and then using a locate to find 1 record

better would be to have a query with only loading the specific record using "select * from table where id = :id"

also try and use bind variables
and don't create your queries like "select * from table where id = 2"

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.