Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I query between two columns while still taking advantage of indexes?

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)
like image 938
Chip Avatar asked Sep 17 '11 00:09

Chip


People also ask

How do indexes on multiple columns work?

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.

Is indexes allowed in multiple columns?

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.

Can a query use two indexes?

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.

When we combine multiple columns in a single index it is known as a index?

A concatenated index, also known as multi-column, composite or combined index, is one index across multiple columns.


1 Answers

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.

like image 143
Bohemian Avatar answered Nov 15 '22 04:11

Bohemian