r9, this does work as far as getting the results goes.
But it sends MySQL usage from typical of 3.5% of CPU to 105% of CPU and it took about 2 Minutes for answers to come back and that was with LIMIT only at 10. And we need typical such emails to be at 5000 or so LIMIT
So obviously this SELECT as is is not practical, any ideas to get it to perform like typical SELECT which returns result in under 0.02 sec and barely effects CPU?
Of course we know about Indexed, but questions are:
1- Which fields to Index
2- And should we shut down a DB while it is being Indexed
3- Will it really make that much of a difference since our typical select returns results in 0.005 second for 10000+ records and this select took 2 minutes for 10 records
r9, sorry for long delay in replying. This is what you get for doing 100 peoples job.
Anyway, to your points:
Can you point me to what you consider to be best writeup on Mysql Indexing as it pertains to above.
And how would you issue such as command and what options should we worry about?
r9, just wanted to say Thanks on this Index heads up.
It has been a real Eye opener to delve deep into Indexs and boy we made some SELECTs much faster. Like 100 times faster! But one Key thing I am having difficult grasping with is:
Which Columns to Index? For best results. I mean what is best logic for deciding this?