By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 712 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

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

P: 1
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
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
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.