Re: query not using index

[gigi kent]

Here they are:

| geotable | CREATE TABLE `geotable` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `accuracy` smallint(6) NOT NULL DEFAULT '0',
  `activity` tinyint(4) NOT NULL DEFAULT '0',
  `lat` double(9,8) NOT NULL,
  `lon` double(9,8) NOT NULL,
  `language` char(2) NOT NULL DEFAULT 'en',
  `sex` char(1) NOT NULL DEFAULT 'm',
  `locality` varchar(128) NOT NULL DEFAULT 'undisclosed',
  `country` char(4) NOT NULL DEFAULT '',
  `province` varchar(32) NOT NULL DEFAULT '',
  `city` varchar(32) NOT NULL DEFAULT '',
  `text` varchar(32) DEFAULT 'some nickname',
  `status` varchar(64) DEFAULT 'ask me',
  `time` bigint(13) NOT NULL,
  UNIQUE KEY `uk_uid` (`uid`),
  KEY `lat_lon_time` (`lat`,`lon`,`time`),
  KEY `ctry_time` (`country`,`time`),
  KEY `time_lang` (`time`,`language`),
  KEY `time_sex` (`time`,`sex`),
  CONSTRAINT `geotable_constr_1` FOREIGN KEY (`uid`) REFERENCES `userstable` (`uid`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=700555 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |

| userstable | CREATE TABLE `userstable` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(2049) NOT NULL,
  `sha1_user_id` char(40) CHARACTER SET ascii NOT NULL,
  `user_pw` varchar(255) DEFAULT NULL,
  `acc_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `last_logout` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `online_status` tinyint(4) DEFAULT '0',
  `failed_logins` int(11) DEFAULT '0',
  `account_status` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `sha1_user_id` (`sha1_user_id`),
  KEY `user_pw` (`user_pw`),
  KEY `user_id` (`user_id`(255)),
  KEY `last_login` (`last_login`),
  KEY `last_logout` (`last_logout`)
) ENGINE=InnoDB AUTO_INCREMENT=700562 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |

| userstable    | InnoDB |      10 | Compact    |  501374 |            130 |    65634304 |               0 |    121487360 | 240123904 |         700562 | 2013-07-05 13:04:10 | NULL                | NULL       | utf8_general_ci   |     NULL | row_format=DYNAMIC |         |


| geotable | InnoDB |      10 | Compact    |  199917 |            102 |    20496384 |               0 |     38273024 | 240123904 |         700555 | 2013-10-24 06:45:56 | NULL                | NULL       | utf8_general_ci   |     NULL | row_format=DYNAMIC

Thanks for the link, it has been checked already :)
For now, I'll stick to just making these indexes work.