Forum Moderators: open

Message Too Old, No Replies

Speeding up select query with indexing on two columns

         

Wayder

6:59 pm on Apr 18, 2023 (gmt 0)

10+ Year Member Top Contributors Of The Month



I have a table low, high, code, name with an index on low, high.

CREATE TABLE table(
Low int(10) UNSIGNED NOT NULL,
High int(10) UNSIGNED NOT NULL,
Code varchar(5) NOT NULL,
Name varchar(40) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;";


I then add an index for Low & High


ALTER TABLE table ADD KEY nums (Low,High) USING BTREE;


I SELECT using the following


SELECT Code,Name
FROM table
WHERE Low <= ?
AND High >= ?


It’s a large table and the SELECT just not fast enough.

Does anyone have any ideas how I can speed the SELECT up?
Have I messed up the indexing, Is there a better way?

Thank you