Index Search over 26 times slower than full table scan

[Christian Zintl]

I have a table with 83376 rows which stores events/gigs. When I try to query gigs with `gig_date` >= curdate() the query takes about 14 seconds when using the index on the `gig_date` column, but only 0.3 seconds with 'ignore index(`date`)'

Doing the same on InnoDB takes below 0.5 seconds both ways. The Optimizer on mySQL 5.6 with InnoDB decides to to a full table scan rather than index search.

The query is:
SELECT * FROM `gig` ignore index(`date`)
WHERE `gig_date` >= CURDATE() AND `location_id` IN
(SELECT `location_id` FROM `location` WHERE `city_id` IN
(SELECT `city_id` FROM `city` WHERE `name` LIKE '%Nürnberg%'))
ORDER BY `gig_date`

I tried:
SELECT * FROM `gig` force index(`date`) ... WHERE `name` ='Nürnberg'... 14 seconds.
SELECT * FROM `gig` force index(`date`)... WHERE `city_id` IN (31455,31456)) ... 10 seconds
SELECT * FROM `gig` force index(`date`) WHERE `gig_date` >= CURDATE() AND `location_id` IN (5,6......) ... 0.3 seconds

I also tried:
select g.* from gig g
join location l using(`location_id`)
join city c using(`city_id`)
where g.gig_date >= curdate() AND c.`name` LIKE '%Nürnberg%'

14 seconds

and:
select g.* from gig g ignore index(`date`)
join location l using(`location_id`)
join city c using(`city_id`)
where g.gig_date >= curdate() AND c.`name` LIKE '%Nürnberg%'

0.3 seconds


Is using an index with a "greater than" operation on a BTREE that much slower? And why? Or is that only a problem with the 'date' type?
Why is the optimizer in mySQL 5.6 smarter than in mySQL Cluster 7.3.5

I can live with the workaround of putting "igonre index" in, but stuff like that costs a lot of time to find out - and if I had not had the mySQL 5.6 as a reference, I'd probably still be searching for a solution.