How do you go from a 6 seconds query to a 0.0001 second one? Well it seems hinting, which is an Oracle thing and is not so well documented for MySQL, works anyway. Just adding this hint /*+FIRST_ROWS(10)*/ to a select statement dramatically reduces query time. From the person who gave me that tricks, this tells MySQL to start sending the result as soon as it gets x rows back, and not to wait for the query to complete.
A 6 seconds query returns results in less than a milisecond with
SELECT /*+FIRST_ROWS(10)*/
For big result sets, this is a significant advantage.
It does not work with GROUP BY statements, and I’m not sure if it does with ORDER BY. It would be logical that it doesn’t, as MySQL needs the whole result set before it can start sorting, thus bringing the hinting help to zero.
