Converting between clause to joins [duplicate]

问题:

This question is an exact duplicate of:

• How to use indexes for a query filtering BETWEEN two columns 2 answers

Need a generic sql query for joins that can run on mysql, postgres and other major DBs

I have a table named autumn4.ip , I have three columns: id,start,end (all ints).

How do I make a join so that I make use of BUT not by BETWEEN but by JOINS.

like pseudo:

select * FROM autumn.ip WHERE :number-constant >= start ORDER BY start DESC LIMIT 1; +

select * FROM autumn.ip WHERE :number-constant <= end ORDER BY start ASC LIMIT 1;

how to some one join the above two queries to get one row of data that would be equivalent to

SELECT * FROM autumn4.ip WHERE :number-constant BETWEEN START AND END LIMIT 1;

Just dont ask my why I am doing it :) but I know you woudl so:

I am trying to accomplish is to use 2 indexes, start and end. if i use BETWEEN and/or a composite index of start,end. Only start index is used and not the end. now please dont tell me that I am wrong. I just want it that way. I have done alot of research on this

if i do create single indexes for both start and end, only one is used. give me a query in which both are used

回答1:

What you have heard about range scans and between in MySQL is simply not true. Here is a quote from the documentation:

B-Tree Index Characteristics

A B-tree index can be used for column comparisons in expressions that use the =, >, >=, <, <=, or BETWEEN operators. The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

So, the use of an index is not surprising. The best index for this data would be a composite index on start, end.

The problem that you have is that you have two columns start and end. This makes the query a bit more complicated.

The following could conceivably perform better under some circumstances, with an index on start and a separate index on end and a primary key on the rows:

select gt.*
from (SELECT * FROM autumn4.ip WHERE :number >= start) gt join 
     (SELECT * FROM autumn4.ip WHERE :number <= end) lt
     on gt.PRIMARYKEY = lt.PRIMARYKEY;

回答2:

You want that the request selects only the 1st row between start and end, using JOINs ?

EDIT:

forget my answer! No need for 2 tables for what I thought! It's more an index problem than anything else.....

You can try to put an index on START, and a different one on END (it seems that you have 1 index with both columns, don't you?), and do your select like this:

SELECT * FROM autumn4.ip WHERE :number >= START AND :number <= END ORDER BY START LIMIT 1; 

(or ORDER BY START DESC if you want the biggest start) .

.