***BOGO*** Re: optimize

["Parvesh Garg" <parveshgarg@xxxxxxxxx>]

Hi Krishna:

255629, 267072 is approximately the number of rows that you have in your
tables. You are right that you have primary key indexes and as you can see
they are being used.

Actually speaking, I don't get what the problem is? In the first query you
want all the primary keys of first table to be matched to their
corresponding values in table 2, that's why you have all the rows of table 1
(i.e., 255629) and only 1 corresponding row in table 2.

In the second query you are matching all the primary keys of table 1 with
all the keys of table 2 that are not the equal to it. So you get the first
value matched with all the second values.

Can you be please more clear with what do you want to achieve? I mean what
is the intention behind the query and what is the final result expected? Is
it to find those rows in table 1 that do not have a corresponding row in
table 2 and otherwise?

Regards,
Parvesh Garg

On Wed, May 28, 2008 at 12:51 PM, Krishna Chandra Prajapati <
prajapatikc@xxxxxxxxx> wrote:

> 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,
>