Verwendung von Indizes für eine Abfrage, die BETWEEN zwischen zwei Spalten filtert

Ich brauche eine generische SQL-Abfrage für Joins, die auf MySQL, Postgres und anderen wichtigen DBs ausgeführt werden können.

Ich habe eine Tabelle namens autumn4.ip mit drei Spalten: id, start, end (alle ints).

Wie mache ich einen Join, damit ich ihn nutzen kann, ABER nicht durch BETWEEN sondern durch JOINs?

Wie (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; 

So fügen Sie die obigen beiden Abfragen ein, um das Äquivalent zu erhalten:

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

Ich versuche 2 Indizes für start und end . Wenn ich BETWEEN und / oder einen zusammengesetzten Index für (start, end) , wird nur der Start-Index verwendet und nicht das Ende. jetzt sag mir bitte nicht, dass ich falsch liege. Ich will es nur so. Ich habe viel darüber geforscht.

Wenn ich einzelne Indizes für Start und Ende erstelle, wird nur einer verwendet. Ich suche nach einer Abfrage, die beide verwendet.

Solutions Collecting From Web of "Verwendung von Indizes für eine Abfrage, die BETWEEN zwischen zwei Spalten filtert"

Eine einfache

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

sollte arbeiten.

Wie auch immer, lassen Sie mich Ihre Forschungsqualität in Frage stellen …

Der Trick besteht darin, die zweite Spalte in einem zusammengesetzten Index in der Reihenfolge DESCENDING .

Mit einem Basis-B-Tree-Index würde dies in PostgreSQL verwendet werden (wenn einige allgemeine Voraussetzungen erfüllt sind):

 CREATE INDEX foo ON autumn.ip (col_start, col_end DESC ) 

Der allgemeine Prinzipal sollte für jedes RDBMS mit B-Tree-Indizes gelten.

Auch für Bereichsabfragen wie Ihre ist ein GiST-Index in PostgreSQL möglicherweise effektiver. Betrachten Sie diese nahe verwandte Antwort:
PostgreSQL-Index wird nicht für Abfrage nach Bereich verwendet

Beiseite: Verwenden Sie niemals reservierte Wörter wie start oder end als Bezeichner, insbesondere wenn Sie versuchen, mit mehreren RDBMS kompatibel zu bleiben.