***BOGO*** optimize

["Krishna Chandra Prajapati" <prajapatikc@xxxxxxxxx>]

Hi,

CREATE TABLE `user_info` (
  `user_id` decimal(22,0) NOT NULL default '0',
  `drivers_license` varchar(24) NOT NULL default '',
  `login_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `course_id` decimal(22,0) NOT NULL default '0',
  `regulator_id` decimal(22,0) NOT NULL default '0',
  `test_info` varchar(16) default NULL,
  `completion_date` datetime default NULL,
  `print_date` datetime default NULL,
  `password` varchar(32) default NULL,
  `certificate_number` varchar(16) default NULL,
  PRIMARY KEY  (`user_id`),
  KEY `idx_certificate_number` (`certificate_number`),
  KEY `idx_completion_date` (`completion_date`),
  KEY `idx_print_date` (`print_date`),
  KEY `idx_user_info` (`drivers_license`),
  KEY `idx_user_info_2` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `user_authentication_date` (
  `user_id` decimal(22,0) NOT NULL default '0',
  `authentication_date` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`user_id`),
  KEY `user_authentication_comp1` (`user_id`,`authentication_date`),
  CONSTRAINT `fk_user_authentication_date` FOREIGN KEY (`user_id`)
REFERENCES `user_info` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> explain select ui.user_id from user_info ui,user_authentication_date
uad where ui.user_id=uad.user_id;
+----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+
| id | select_type | table | type   | possible_keys                     |
key     | key_len | ref             | rows   | Extra       |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | uad   | index  | PRIMARY,user_authentication_comp1 |
PRIMARY | 10      | NULL            | 255629 | Using index |
|  1 | SIMPLE      | ui    | eq_ref | PRIMARY                           |
PRIMARY | 10      | dip.uad.user_id |      1 | Using index |
+----+-------------+-------+--------+-----------------------------------+---------+---------+-----------------+--------+-------------+

Why it is scanning 255629 rows, Since user_id in both the table is a primary
key (indexed). Please tell me how can i optimize this.

mysql> explain select ui.user_id from user_info ui,user_authentication_date
uad where ui.user_id!=uad.user_id;
+----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key                 |
key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | uad   | index | NULL          | PRIMARY             |
10      | NULL | 255629 | Using index              |
|  1 | SIMPLE      | ui    | index | NULL          | idx_completion_date |
9       | NULL | 267072 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------------------+---------+------+--------+--------------------------+

-- 
Krishna Chandra Prajapati
MySQL DBA,