Re: Join and order by between two tables in two databases
Yes I agree with you, it should not happen.
The EXPLAIN command gives the same output for both:
+----+-------------+-------+--------+-----------------------------------+---------------+---------+------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+---------------+---------+------------------+--------+----------------------------------------------+
| 1 | SIMPLE | du | range | PRIMARY,searchedSex,searchedSex_3 | searchedSex_3 | 9 | NULL | 104688 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | ug | eq_ref | PRIMARY,birthyear | PRIMARY | 8 | test.du.id | 1 | Using where |
| 1 | SIMPLE | di | eq_ref | PRIMARY | PRIMARY | 8 | test.du.photo_id | 1 | |
+----+-------------+-------+--------+-----------------------------------+---------------+---------+------------------+--------+----------------------------------------------+
The tables have those structures:
CREATE TABLE `DatingUser` (
`id` bigint(20) NOT NULL default '0',
`photo_id` bigint(20) NOT NULL default '0',
`weight` int(3) unsigned default NULL,
`silhouette` tinyint(3) unsigned default NULL,
`hairColor` tinyint(3) unsigned NOT NULL default '0',
`eyesColor` tinyint(3) unsigned NOT NULL default '0',
`origin` tinyint(3) unsigned default NULL,
`size` int(3) unsigned default NULL,
`maritalStatus` tinyint(3) unsigned default NULL,
`studies` tinyint(3) unsigned default NULL,
`occupation` tinyint(3) unsigned default NULL,
`astrologySign` tinyint(3) unsigned default NULL,
`interests` bigint(20) default '0',
`goals` bigint(20) default '0',
`visualizationCounter` int(10) unsigned NOT NULL default '0',
`smoker` tinyint(3) unsigned default NULL,
`keywords` char(255) NOT NULL default '',
`keywordsValid` enum('true','false') NOT NULL default 'true',
`otherInterest` char(255) NOT NULL default '',
`otherInterestValid` enum('true','false') NOT NULL default 'true',
`region` int(10) unsigned default NULL,
`connected` enum('true','false') NOT NULL default 'false',
`searchedSex` enum('M','F','B') NOT NULL default 'B',
`style` tinyint(4) default '0',
`hasNewFlash` enum('true','false') NOT NULL default 'false',
`hasNewMutualFlash` enum('true','false') NOT NULL default 'false',
PRIMARY KEY (`id`),
KEY `hairColor` (`hairColor`),
KEY `eyesColor` (`eyesColor`),
KEY `searchedSex` (`searchedSex`),
KEY `photo_id` (`photo_id`),
KEY `searchedSex_3` (`searchedSex`,`id`,`photo_id`),
FULLTEXT KEY `keywords` (`keywords`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `DatingImage` (
`IMAGEID` bigint(20) NOT NULL auto_increment,
`NAME` char(255) NOT NULL default '',
`WIDTH` int(11) NOT NULL default '-1',
`HEIGHT` int(11) NOT NULL default '-1',
`SINCE` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`STATUS` enum('GRANTED','DENIED','BLACKLIST','STANDBY') NOT NULL default 'STANDBY',
`MODERATORID` int(11) NOT NULL default '0',
PRIMARY KEY (`IMAGEID`),
KEY `STATUS` (`STATUS`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
CREATE TABLE `User` (
`id` bigint(20) NOT NULL auto_increment,
`contactCardId` int(11) default NULL,
`msisdn` char(15) NOT NULL default '',
`email` char(150) default NULL,
`alias` char(10) NOT NULL default '',
`lang` char(2) NOT NULL default 'fr',
`country` char(4) NOT NULL default 'FR',
`info` char(160) default NULL,
`birthyear` int(10) unsigned default NULL,
`sex` enum('M','F') NOT NULL default 'M',
`postcode` char(12) default NULL,
`password` char(10) default NULL,
`password2` char(10) default NULL,
`provider` char(15) default 'FR',
`timeZone` char(15) default NULL,
`universe` tinyint(4) default '0',
`filterStatus` bigint(20) default '-1',
PRIMARY KEY (`id`),
UNIQUE KEY `msisdn` (`msisdn`),
KEY `alias` (`alias`),
KEY `postcode` (`postcode`),
KEY `birthyear` (`birthyear`),
FULLTEXT KEY `info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
The version used was MySql 4.0.18 on Linux (Red Hat Enterprise Linux ES release 3 (Taroon Update 2)).
The main strange thing is that, at a time, by doing nothing, the phenomena has stopped. The only thing I know is that there were other servers on the same machine that take memory...
Francois.