select - MySQL optimalization -

select - MySQL optimalization -

i have 1 table in mysql 7 500 000 rows , have problem duration of query. can help me tips create faster? table called "attendance". has indexses on col movement_date, employee_id , tried create index on both of cols in one. query below takes 9 seconds , slow me. has soma tips? need create select more 14 employee ids.

select * attendance movement_date >= '2014-09-01 00:00:00' , movement_date <= '2014-10-01 23:59:59' , employee_id in (14 integer ids...);

table definition:

create table `attendance` ( `attendance_id` int(10) unsigned not null auto_increment, `attendance_raw_id` int(10) unsigned default null, `employee_id` int(11) not null, `shift_type_id` int(11) default null, `shift_plan_id` int(11) default null, `record_status_id` tinyint(4) default ''1'', `movement_date` datetime default null, `attendance_movement_type_id` tinyint(4) default null, `note` varchar(50) default null, `created_d` datetime default null, `created_w` int(11) default null, `updated_w` int(11) default null, primary key (`attendance_id`), unique key `idx_attendance_raw_id` (`attendance_raw_id`), key `idx_employee_id` (`employee_id`), key `fk_attendance3` (`attendance_movement_type_id`), key `idx_movement_date` (`movement_date`), key `fk_attendance4` (`record_status_id`), key `fk_movement_date_employee` (`movement_date`,`employee_id`), constraint `fk_attendance` foreign key (`employee_id`) references `employee` (`employee_id`), constraint `fk_attendance2` foreign key (`attendance_raw_id`) references `attendance_raw` (`attendance_raw_id`), constraint `fk_attendance3` foreign key (`attendance_movement_type_id`) references `attendance_movement_type` (`attendance_movement_type_id`), constraint `fk_attendance4` foreign key (`record_status_id`) references `record_status` (`record_status_id`) ) engine=innodb auto_increment=9072724 default charset=utf8 checksum=1 delay_key_write=1 row_format=dynamic

thanks!

to improve speed of result need alter sql statement in way

select * attendance movement_date >= '2014-09-01 00:00:00' , movement_date <= '2014-10-01 23:59:59' , employee_id in( select indexed_column_ids table2 ... )

starting number of records, in predicate on select becomes faster on list of constants.

mysql select optimization large-data