Estou usando um banco de dados MySQL e tenho a seguinte tabela:

CREATE TABLE SomeTable (
  PrimaryKeyCol BIGINT(20) NOT NULL,
  A BIGINT(20) NOT NULL,
  FirstX INT(11) NOT NULL,
  LastX INT(11) NOT NULL,
  P INT(11) NOT NULL,
  Y INT(11) NOT NULL,
  Z INT(11) NOT NULL,
  B BIGINT(20) DEFAULT NULL,
  PRIMARY KEY (PrimaryKeyCol),
  UNIQUE KEY FirstLastXPriority_Index (FirstX,LastX,P)
) ENGINE=InnoDB;

A tabela contém 4,3 milhões de linhas e nunca muda uma vez inicializada.

As colunas importantes desta tabela sãoFirstX, LastX, Y, Z eP.

Como você pode ver, eu tenho um índice exclusivo nas linhasFirstX, LastX eP.

As colunasFirstX eLastX defina um intervalo de números inteiros.

A consulta que preciso executar nesta tabela busca por um determinado X todas as linhas com FirstX <= X <= LastX (ou seja, todas as linhas cujo intervalo contém o número de entrada X).

Por exemplo, se a tabela contiver as linhas (estou incluindo apenas as colunas relevantes):

FirstX     LastX      P        Y         Z
------     ------     -       ---       ---
100000     500000     1       111       222 
150000     220000     2       333       444
180000     190000     3       555       666
550000     660000     4       777       888   
700000     900000     5       999       111 
750000     850000     6       222       333 

e eu preciso, por exemplo, das linhas que contêm o valor185000, o primeiro3 linhas devem ser retornadas.

A consulta que tentei, que deveria estar usando o índice, é:

SELECT P, Y, Z FROM SomeTable WHERE FirstX <= ? AND LastX >= ? LIMIT 10;

Mesmo sem o LIMIT, essa consulta deve retornar um pequeno número de registros (menor que50) para qualquer X.

Esta consulta foi executada por um aplicativo Java para120000 valores de X. Para minha surpresa, assumiu10 horas (!) e o tempo médio por consulta foi0,3 segundos.

Isso não é aceitável, nem mesmo aceitável. Deve ser muito mais rápido.

Examinei uma única consulta que levou0,563 segundos para garantir que o índice estava sendo usado. A consulta que eu tentei (a mesma da consulta acima com um valor inteiro específico em vez de?) devolvida2 linhas.

eu useiEXPLAIN para descobrir o que estava acontecendo:

id               1
select_type      SIMPLE
table            SomeTable 
type             range
possible_keys    FirstLastXPriority_Index
key              FirstLastXPriority_Index 
key_len          4
ref              NULL
rows             2104820
Extra            Using index condition

Como você pode ver, a execução envolvida2104820 linhas (quase 50% das linhas da tabela), mesmo que apenas duas linhas satisfaçam as condições, portanto, metade do índice é examinada para retornar apenas 2 linhas.

Há algo de errado com a consulta ou o índice? Você pode sugerir uma melhoria na consulta ou no índice?

EDITAR:

Algumas respostas sugeriram que eu execute a consulta em lotes para vários valores de X. Não posso fazer isso, pois executei essa consulta em tempo real, pois as entradas chegam ao meu aplicativo. Cada vez que uma entrada X chega, devo executar a consulta para X e executar algum processamento na saída da consulta.