Re: Mysql 5.5 and 5.6 slower than 5.0 and 5.1 on selected query
> All tables is innodb.
Where they InnoDB on 5.0 and 5.1? (The default has changed from MyISAM to InnoDB.)
> Did you try creating a scj_ships index on (ships_id, date)?
No, it needs to be the other order. Here's why...
The only filtering in the WHERE clause is on `date`, so the optimizer is likely to want to start there.
(date, ships_id) would be good because it would do a range scan of the index, then be finished.
Notice that that is what happened ("Using index"). So, I would guess that there is an INDEX(date, ships_id).
I, too, want to see SHOW CREATE TABLE and SHOW TABLE STATUS.
Also, please provide the EXPLAIN from the old server.
And SHOW VARIABLES LIKE '%buffer%' on both old and new.