How do I create a clustered index and use it to speed up my MySQL query

[frosturi]

I have a very slow SQL statement where I am trying to grab a chunk of data for a given time/date frame from one table. My table is getting quite large and the query is taking too long. Sometimes 10 seconds to grab just 12 data points. I came across this earlier post:

http://bytes.com/topic/sql-server/an...slow-sql-query

and it seems like if I do some smart indexing then, I can solve my problem. I am very new to the world of databases and don't know how to create a clustered index and secondly how to use one.

Below is my query.

SELECT collect_time, sensor_id, value 
FROM scalar_value_measurements 
WHERE sensor_id = 254 
AND collect_time BETWEEN '2012-12-03 07:52:57' 
AND NOW() 
ORDER BY collect_time ASC

I've tried taking out the ORDER BY, but it does not speed things up.

Below is the MySQL statement used to create the table that I'm querying.

CREATE  TABLE IF NOT EXISTS `test`.`scalar_value_measurements` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `collect_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `value` FLOAT(11) NOT NULL ,
  `sensor_id` BIGINT(20) UNSIGNED NOT NULL ,
  PRIMARY KEY (`id`, `sensor_id`) ,
  UNIQUE INDEX `id` (`id` ASC) ,
  INDEX `fk_scalar_value_measurements_sensor_config1` (`sensor_id` ASC) ,
  INDEX `collect_time` USING BTREE (`collect_time` ASC) ,
  INDEX `sensor_id` USING BTREE (`sensor_id` ASC) ,
  CONSTRAINT `fk_scalar_value_measurements_sensor_config1`
    FOREIGN KEY (`sensor_id` )
    REFERENCES `test`.`sensor_config` (`sensor_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 5985843
DEFAULT CHARACTER SET = utf8
-- COLLATE = latin1_swedish_ci;
COLLATE = utf8_general_ci;

[Rabbit]

Unless your table has a ton of records that you don't need to return, I don't see any reason for your query to be slow. It's a fairly simple query.

MySql uses the primary key as the clustered index. To create your clustered index, add the fields you want included in your cluster to the primary key. You can also try a secondary index first so that you don't have to modify the primary key.