Left join/inner join question

Databases
1

Hi guys!

I am trying to select all users from a database, whilst also selecting 2 additional fields from 2 seperate tables.

The query I am using is this…However, it only returns 4,000 rows, when there are over 20,000 rows in the users table.


SELECT users.*, locations.locationsName, userscv.cvFile 
FROM users 
LEFT JOIN locations ON locations.locationsId = users.usersLocationId 
INNER JOIN userscv ON userscv.usersId = users.usersId 
GROUP BY users.usersId

Here are the tables:


CREATE TABLE IF NOT EXISTS `users` (
  `usersId` int(11) NOT NULL auto_increment,
  `usersName` varchar(100) character set utf8 NOT NULL default '',
  `usersAddress1` varchar(100) character set utf8 default NULL,
  `usersAddress2` varchar(100) character set utf8 default NULL,
  `usersStateId` int(11) default '0',
  `usersLocationId` int(11) default '0',
  `usersCountryId` int(11) default '0',
  `usersZip` varchar(20) character set utf8 default NULL,
  `usersUsername` varchar(100) character set utf8 NOT NULL default '',
  `usersPassword` varchar(100) character set utf8 NOT NULL default '',
  `usersEmail` varchar(100) character set utf8 NOT NULL default '',
  `usersTown` varchar(100) character set utf8 default NULL,
  `usersDob` date default NULL,
  `usersGender` enum('NA','Male','Female') character set utf8 default 'NA',
  `usersWorkPhone` varchar(35) character set utf8 default '0',
  `usersMobilePhone` varchar(35) character set utf8 NOT NULL default '0',
  `usersHomePhone` varchar(35) character set utf8 NOT NULL default '0',
  `usersAllowCVSearch` tinyint(11) NOT NULL default '1',
  `usersCVText` longtext character set utf8 NOT NULL,
  `usersLastUpdated` date NOT NULL default '0000-00-00',
  `usersMailingList` enum('yes','no') character set utf8 NOT NULL default 'yes',
  `usersCvMatching` enum('yes','no') character set utf8 NOT NULL default 'yes',
  PRIMARY KEY  (`usersId`),
  KEY `usersCvMatching` (`usersCvMatching`),
  FULLTEXT KEY `usersCVText` (`usersCVText`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=34893 ;

CREATE TABLE IF NOT EXISTS `userscv` (
  `cvId` int(10) unsigned NOT NULL auto_increment,
  `cvName` varchar(250) default NULL,
  `cvDate` datetime default NULL,
  `cvFile` varchar(150) default NULL,
  `usersId` int(10) unsigned default NULL,
  `usersCVText` longtext,
  `usersAllowCVSearch` tinyint(11) unsigned default '1',
  `Status` varchar(45) default 'active',
  PRIMARY KEY  (`cvId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=11462 ;

CREATE TABLE IF NOT EXISTS `locations` (
  `locationsId` int(11) NOT NULL auto_increment,
  `locationsName` varchar(255) NOT NULL default '',
  `locationsStatus` varchar(45) default 'active',
  PRIMARY KEY  (`locationsId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 ;
2

Do all users have a locationid that exists in the locations table? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.

Do all users have a userid that exists in the cv table (= do all users have a cv)? If yes, you can use an INNER JOIN, if no (and you want all users) use a LEFT JOIN.

3

Not all users have a locationid or a userid that exists in the cv table. Does this mean I should use LEFT JOIN on both? I tried this earlier from phpMyadmin, but it took ages for it to load and I ended up just cancelling it while it was loading.

Any idea why it takes so long?

4

Yes, use LEFT JOIN on both.
Try putting an index on usersLocationId.