What are the optimization rules (if any) when constructing the WHERE clause in SELECT expression? (Target system is MySQL)
Order of statements, etc.
That is of no help, I read it.
"Work on the MySQL optimizer is ongoing, so this section is incomplete."
What I was asking is - are there any undocumented issues one should follow to speed up the row fetching.
Generally if something is undocumented, unless you already know what you're doing, you probably shouldn't be following it.
MySQL's query optimizer is a piece of junk. -.-
>>1
Seeing as you obviously aren't familiar with the subject - look at the execution plan, understand it, optimize where possible.
Just by doing TheRightThing™ you should get 98% - 100% of speed.
>>6
And you obviously didn't understand my question and just trying to look cool, lol.
"execution plan", wtf?
Many database engines will let you see the "execution plan" of a query, showing you exactly how the engine interprets your query and intends to execute it, which lets you find potential bottlenecks. Off the top of my head I don't know the command for this in MySQL. Maybe the EXPLAIN keyword? But anyways you should check out its support for execution plans.
>>7
And you obviously don't understand sql and just trying to look cool, lol.
"read some books", wtf?
>>9
And where exactly i was trying to look cool? =)
Actually, I was asking for help. And what I got?
"Just by doing TheRightThing™" shit is so helpful.
back to the topic: the problem (too slow queue execution) was due to overusage of server resources :-/
>>10
Asking about the query optimizer and undocumented behavior just makes you sound like you overheard someone talking about sql once and picked up a couple of keywords. It also makes other people not really care about your thread.
On the other hand, being forthright and saying "I don't know what I'm doing", and also demonstrating that you are capable of using both a search engine and your brain, might get you somewhere.
I'm guessing based on your last comment that you're using some cheap/free web host, in which case my first suggestion would be to get a better host (perhaps set up your own server locally; you'll learn a lot that way). But again, you've supplied absolutely no information, so this is a total guess.
>>11
All guesses are wrong, but nah, lets stop this. Pointless.
Those were my first posts in a long-long time, so yeah, i had to provide some more info, my bad.
OP, out.
>>11 are you dense, are you retarded or something? OP was asking about MySQL. Was he fucking asking about SQL? Goddamnit.
Now to something completely different, what are you blind? Was he asking to bitch a bit more? Is it too hard to stay on the topic for you?
Because, you are talking nonsense. You make no attempt to help somehow, it looks like you are full of shit.
LOOK I'M KEWL PROGRAMMER AND THEREFORE U SUCK - this is what you are trying to be.
In short, be helpful already.
>>13
Frankly, the question sucked. Is this a single table query? OUTER JOIN across tables that should be a JOIN? 9 tables and 3 views in one query with aggregates? On the MySQL front, which version? Which engine[s] is/are the table[s] using?
The only general rules are
1) "if you search on a column, you probably want to index that column"
2) "if you're doing it wrong, EXPLAIN might tell you you're doing it wrong, but won't tell you how to do it right"
3) "If you have a SELECT inside of a loop, you're 99% certain to be doing it wrong. Especially if it's something you should have done with [OUTER] JOIN."
Everything else depends on what the hell you're doing. If you're doing JOINs, you might get better performance moving expressions from the WHERE clause to the appropriate ON clause, but not by much. ANDs are generally evaluated faster than ORs (since any record rejected by the first AND is dropped from any further consideration), I'm not sure if MySQL's query planner converts NOT (x OR y OR z) to (NOT x AND NOT y AND NOT z) Frankly, though, if you're trying to tune your WHERE clause, it's because you're missing an index. It takes the server so many more times worth of work to join and sort your tables that working out whether the server looks at "x=3" or "y='ilikepie'" first is pointless.
And yeah, >>10 if the server is slow, your query will be slow. Just make sure it isn't you making the server slow.