468,544 Members | 1,771 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,544 developers. It's quick & easy.

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

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.

Expand|Select|Wrap|Line Numbers
  1. SELECT collect_time, sensor_id, value 
  2. FROM scalar_value_measurements 
  3. WHERE sensor_id = 254 
  4. AND collect_time BETWEEN '2012-12-03 07:52:57' 
  5. AND NOW() 
  6. 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.

Expand|Select|Wrap|Line Numbers
  1. CREATE  TABLE IF NOT EXISTS `test`.`scalar_value_measurements` (
  2.   `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
  3.   `collect_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  4.   `value` FLOAT(11) NOT NULL ,
  5.   `sensor_id` BIGINT(20) UNSIGNED NOT NULL ,
  6.   PRIMARY KEY (`id`, `sensor_id`) ,
  7.   UNIQUE INDEX `id` (`id` ASC) ,
  8.   INDEX `fk_scalar_value_measurements_sensor_config1` (`sensor_id` ASC) ,
  9.   INDEX `collect_time` USING BTREE (`collect_time` ASC) ,
  10.   INDEX `sensor_id` USING BTREE (`sensor_id` ASC) ,
  11.   CONSTRAINT `fk_scalar_value_measurements_sensor_config1`
  12.     FOREIGN KEY (`sensor_id` )
  13.     REFERENCES `test`.`sensor_config` (`sensor_id` )
  14.     ON DELETE NO ACTION
  15.     ON UPDATE NO ACTION)
  16. ENGINE = InnoDB
  17. AUTO_INCREMENT = 5985843
  18. DEFAULT CHARACTER SET = utf8
  19. -- COLLATE = latin1_swedish_ci;
  20. COLLATE = utf8_general_ci;
Dec 3 '12 #1
1 2082
Rabbit
12,513 Expert Mod 8TB
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.
Dec 3 '12 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Jim | last post: by
3 posts views Thread by Jack A | last post: by
4 posts views Thread by Tryfon Gavriel | last post: by
5 posts views Thread by jim_geissman | last post: by
1 post views Thread by anonieko | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.