Imagine I have a table contains all the chapters of a book and the start/end page from each chapter.
chapter | start_page | end_page
--------------------------------------
1 | 1 | 24
2 | 25 | 67
3 | 68 | 123
4 | 124 | 244
5 | 245 | 323
I'm attempting to find out what chapter a random page falls on, let's say page 215 for example.
My first idea was to use a query like this
SELECT `chapter`
FROM `book`
WHERE `start_page` <= 215
AND `end_page` >= 215
Unfortunately MySQL can not take advantage of indexes in the above query which is a large problem due to the large size of my table.
After doing some research I came up with this query which does take advantage of indexes.
SELECT `chapter`
FROM `book`
WHERE `start_page` <= 215
ORDER BY `start_page` DESC
LIMIT 1
The issue now is I want the ability to query multiple random pages while still taking advantage of indexes. It doesn't seem likely that I can modify my last query since it's so heavily reliant on limiting results to one.
Any advice would be much appreciated!
UPDATE: Thanks to a comment from Ray Toal I have a query which gives me the results I need with amazing performance.
SELECT chapter
FROM book
WHERE (start_page = (SELECT max(start_page) FROM book WHERE start_page <= 73) AND end_page >= 73)
OR (start_page = (SELECT max(start_page) FROM book WHERE start_page <= 92) AND end_page >= 92)
OR (start_page = (SELECT max(start_page) FROM book WHERE start_page <= 300) AND end_page >= 300)
A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned.
MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on.
Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAIN to obtain information about how MySQL executes a statement.
A concatenated index, also known as multi-column, composite or combined index, is one index across multiple columns.
Isn't it as simple as this?
select max(chapter)
from book
where start_page <= 215;
If end pages follow previous start pages, this will work.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With