Hi @KK,
Need your help here:
I wish to add FORCE INDEX (couch_pages_IndexA1), so final query looks like
TL;DR
MySQL execution plan without forced index is perfect (1 row, using index), but as I am going further in my optimizations, I want that queries also run fast on cold run or when out of query cache/buffer pool.
I disabled query cache and set minimal innodb buffer pool size. The result of my experiment is really promising. I found that InnoDB still tries to push something to buffer pool and on cold start this simple query takes 0.5 sec (profiling assigns 99% of the time to 'statistics'). I assume some data from tables go there for any further storage or, more probably, time is consumed for selecting the proper index (we still have a lot of them
). For the hot run the execution time is still the same, it means that my settings are correct and buffers are not used for storing data (only maybe indexes).
As my RAM is much less than total data (even when 50% compressed) I expect some data to be outside of buffers. With forced index on the cold run query takes 0.02 sec, and hot run is as expected 0.0001 sec, which means MySQL reads only index and doesn't try to do anything else (select proper index, cache tables, result sets and so on). This is exactly what I need! Later I will revert to a large buffer pool and enable query cache (which helps immensely). Memory will be less junked and overall things will be much faster (especially with couch_pages indexes).
I am happy with current settings of MySQL, with the way my composite indexes work, and speed of custom queries and step by step I'm getting to the desired outcome.
Need your help here:
$rs = $DB->select( K_TBL_PAGES, array('*'), "template_id='" . $DB->sanitize( $this->tpl_id ). "' AND is_master='1'" );
I wish to add FORCE INDEX (couch_pages_IndexA1), so final query looks like
SELECT * FROM couch_pages FORCE INDEX (couch_pages_IndexA1) WHERE template_id='35' AND is_master='1'
TL;DR
MySQL execution plan without forced index is perfect (1 row, using index), but as I am going further in my optimizations, I want that queries also run fast on cold run or when out of query cache/buffer pool.
I disabled query cache and set minimal innodb buffer pool size. The result of my experiment is really promising. I found that InnoDB still tries to push something to buffer pool and on cold start this simple query takes 0.5 sec (profiling assigns 99% of the time to 'statistics'). I assume some data from tables go there for any further storage or, more probably, time is consumed for selecting the proper index (we still have a lot of them
). For the hot run the execution time is still the same, it means that my settings are correct and buffers are not used for storing data (only maybe indexes).As my RAM is much less than total data (even when 50% compressed) I expect some data to be outside of buffers. With forced index on the cold run query takes 0.02 sec, and hot run is as expected 0.0001 sec, which means MySQL reads only index and doesn't try to do anything else (select proper index, cache tables, result sets and so on). This is exactly what I need! Later I will revert to a large buffer pool and enable query cache (which helps immensely). Memory will be less junked and overall things will be much faster (especially with couch_pages indexes).
I am happy with current settings of MySQL, with the way my composite indexes work, and speed of custom queries and step by step I'm getting to the desired outcome.