maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

On Mon, May 14, 2012 at 03:15:09PM +0200, Markus Pilman wrote:
> > - Do you really records_in_range() implementation like in (*)
> Yes.
> 
> > - Are estimates of 10 and 20 rows close to reality?
> Not at all... The thing is: we can implement records_in_range not as a
> function with constant complexity and since a full table scan is not
> faster than a index range lookup in our storage engine (even if the
> whole table is contained in the range), we wanted to force MySQL to
> make always an index lookup. If I understand everything correctly, my
> error in thinking was, that I thought MySQL uses the information
> returned by records_in_range only to chose between index lookup and
> fulltable scan (since on a traditional disk architecture a full table
> scan will be faster than an index scan which scans the whole table).
> But this is obviously not the case.

As far as I understand you, you're correct. Information returned by
records_in_range() is used in multiple places:
1. It is used to determine whether to make range scan or a full table scan.
2. It affects the order in which tables are joined.
3. (maybe there is something else it affects also)

I think you should be able to force MySQL into doing index lookups instead of
table scans by returing appropriate costs from
handler->keyread_time() and handler->read_time() as opposed to scan_time().

> So we will have to implemented this records_in_range method. Do I
> understand this correct, that a logarithmic (with respect to the table
> size) run time will be ok for this method (InnoDB seems to walk two
> times through the index if I get the comments in the code correct)?

I think logarithmic time should be ok.

Note that records_in_range() can be called multiple times if you have multiple
ranges. For example, for query like

SELECT * FROM tbl WHERE t.key < 11 OR t.key IN (15, 17, 19, 22)

you will get these calls:

  records_in_range(t.key < 11)
  records_in_range(t.key=15)
  records_in_range(t.key=17)
  records_in_range(t.key=19)
  records_in_range(t.key=22)

If you prefer to get all ranges at once, check out
handler::multi_range_read_info_const() in sql/multi_range_read.cc - you can
implement that function and get access to a  disjoint-and-sorted list of ranges.
(however, records_in_range() will still need to be implemented because there
are certain cases where records_in_range() is called directly, not through
multi_range_read_info_const()).

BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog