MySQL Forums
Forum List  »  Performance

Re: Join and order by between two tables in two databases
Posted by: Francois Debly
Date: April 07, 2005 06:46AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Join and order by between two tables in two databases
2701
April 07, 2005 06:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.