troubleshooting Question

Optimum sequence for mysql query operators

Avatar of Bellone
Bellone asked on
MySQL Server
3 Comments2 Solutions418 ViewsLast Modified:
I have an Access 2010 pass-through query to a large MySQL 5.2 backend table. The WHERE clause of the query usually has several of the usual operators : =, >,<, like, between, not null, etc.

I notice that the sequence of the operators in the query makes a big difference to the retrieval times.  For example, putting a 'like' operator before an 'equals' operator, takes four times as long as the other way round.  (All of the relevant fields are indexed.)

The operators are held in a table, and I have it in mind to give them a sequence number, so that VBA constructs the query in the most efficient way

Is there a recognised 'pecking order' for MySQL query operators?
ASKER CERTIFIED SOLUTION
ienaxxx

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros