sql - Index on date field mysql -


i have table screenshot 3 fields:

create table `screenshot` (   `id` int(11) not null auto_increment,   `userid` int(11) not null,   `datetaken` date not null,   primary key (`id`),   key `datetaken` (`datetaken`),   key `userid` (`userid`) using btree,   constraint `userid_foreign_key` foreign key (`userid`) references `users` (`userid`) ) engine=innodb auto_increment=22514871 default charset=latin1 

and

select @@innodb_buffer_pool_size 

result: 16777216

query:

select count(id) total         screenshot datetaken between '2000-05-01' , '2000-06-10' 

result : 2828844

explain output:

id|select_type|   table  |type |possible_keys|   key   |key_len| rows  |extra 1 |  simple   |screenshot|range|  datetaken  |datetaken|  3    |5730138|using where; using index 

here problem: have added index datetaken column , yet scanning rows (explain output) bigger result. seems whole scan table. , query runtime query takes 15 seconds. how can improve speed in query above?

you try adding composite index

  create index test on screenshot (datetaken, id) 

Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -