Forum Moderators: open
SELECT * FROM Results
WHERE Name = 'Chr1'
AND ( (Start >= 16300 AND Start <= 16400)
OR (End >= 16300 AND End <= 16400)
OR (Start < 16300 AND End > 16400) );
This query never completes so I am a bit stuck. I have created a multi index on the Name, Start and End columns using:
ALTER TABLE Results ADD INDEX (Name, Start, End);
If anyone has any ideas how to optimize the query or if I should be creating other indexes help would be much appreciated!
Also here is what an EXPLAIN gives for this query:
+----+-------------+-------------+------+---------------+-------+---------+-------+---------+-------------+
¦ id ¦ select_type ¦ table ¦ type ¦ possible_keys ¦ key ¦ key_len ¦ ref ¦ rows ¦ Extra ¦
+----+-------------+-------------+------+---------------+-------+---------+-------+---------+-------------+
¦ 1 ¦ SIMPLE ¦ Results ¦ ref ¦ Name ¦ Name ¦ 17 ¦ const ¦ 7757498 ¦ Using where ¦
+----+-------------+-------------+------+---------------+-------+---------+-------+---------+-------------+
1 row in set (0.02 sec)