Imagine I've got a table consists of all of the sections of the book and also the start/finish page from each chapter.

chapter    start_page      finish_page


   1            1             24

   2            25            67

   3            68            123

   4            124           244

   5            245           323

I am trying to discover what chapter a random page falls on, let us say page 215 for instance.

My first idea was to utilize a query such as this

Choose `chapter`

FROM `book`

WHERE `start_page` <= 215

AND `end_page` >= 215

Regrettably MySQL cannot make the most of indexes within the above query the industry large problem because of the big size my table.

After doing a bit of research I emerged with this particular query which does make the most of indexes.

Choose `chapter`

FROM `book`

WHERE `start_page` <= 215

ORDER BY `start_page` DESC


The problem now's I would like a chance to query multiple random pages while still benefiting from indexes. It does not appear likely will be able to modify my last query becasue it is so heavily just a few restricting leads to one.

Any advice could be much appreciated!

UPDATE: Because of a comment from Ray Toal I've got a query which provides me the outcomes I want with amazing performance.

Choose chapter

FROM book

WHERE (start_page = (Choose max(start_page) FROM book WHERE start_page <= 73) AND finish_page >= 73)

OR (start_page = (Choose max(start_page) FROM book WHERE start_page <= 92) AND finish_page >= 92)

OR (start_page = (Choose max(start_page) FROM book WHERE start_page <= 300) AND finish_page >= 300)

Is not it as easy as this?

choose max(chapter)

from book

where start_page <= 215

If finish pages follow previous start pages, this works.

Add two composite indices:


    ADD INDEX `page_range_from_start` (start_page, finish_page)

    ADD INDEX `page_range_from_end` (finish_page, start_page)

And proceed together with your original query:

Choose `chapter`

FROM `book`


    `start_page` <= 215

    AND `end_page` >= 215

MySQL will pick the index leading using the column that can offer the fewest remaining rows to scan, after which it'll have another area of the index to lessen towards the single preferred row (with no scan).

Syntactically-valid same as Bohemian's INTERSECT solution (takes a unique index of some type and enormous join buffer):




    book AS book_l

    JOIN book AS book_r

    USING (id)


     book_l.start_page <= 215

     AND book_r.finish_page >= 215

Or perhaps a temptable approach (takes a single index on all of start_page and finish_page):

Choose chapter FROM (

    Choose * FROM book WHERE start_page <= 215


    Choose * FROM book WHERE finish_page >= 215

) AS derived WHERE start_page <= 215 AND finish_page >= 215