MAX_JOIN_SIZE & query takes 4.7 seconds with no other processes running

[kaosweaver]

Here is the error:

 

Error Number: 1104

The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay

SELECT c_name, catid, memberid, lft,rgt FROM (tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5) left JOIN tblMember ON member_id=memberid and memberid=5 WHERE tblCategory.cat_id=25 ORDER BY c_name

 

I do what the error indicates (SET SQL_BIG_SELECTS=1) and the query takes almost 5 seconds.

 

The tables are:

CREATE TABLE `jtMemberCategory` (
  `member_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tblCategory` (
  `catid` int(11) NOT NULL auto_increment,
  `c_name` varchar(250) character set utf8 collate utf8_unicode_ci NOT NULL,
  `c_namefarm` varchar(250) NOT NULL,
  `cat_id` int(11) NOT NULL default '-1',
  `c_desc` text character set latin1 collate latin1_general_ci NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `rank` int(11) NOT NULL default '10',
  PRIMARY KEY  (`catid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `tblMember` (
  `memberid` int(11) NOT NULL auto_increment,
  `m_companyname` varchar(250) NOT NULL,
  `m_contact_name` varchar(50) NOT NULL,
  `m_contact_email` varchar(50) NOT NULL,
  `m_address` varchar(100) NOT NULL,
  `m_address2` varchar(100) default NULL,
  `m_city` varchar(50) NOT NULL,
  `m_state` varchar(2) NOT NULL,
  `m_zip` varchar(10) NOT NULL,
  `m_phone` varchar(20) NOT NULL,
  `m_email` varchar(50) NOT NULL,
  `m_url` varchar(50) NOT NULL,
  `m_password` varchar(10) NOT NULL,
  `m_publicRestroom` varchar(1) NOT NULL default 'N',
  `m_handicapp` varchar(1) NOT NULL default 'N',
  `m_handicappRestroom` varchar(1) NOT NULL default 'N',
  `m_handWashing` varchar(1) NOT NULL default 'N',
  `m_internship` varchar(1) NOT NULL default 'N',
  `m_educationTour` varchar(1) NOT NULL default 'N',
  `m_groupTour` varchar(1) NOT NULL default 'N',
  `m_motorCoaches` varchar(1) NOT NULL default 'N',
  `m_summary` text,
  `m_longSummary` text,
  `m_photo` varchar(100) default NULL,
  `m_featured` varchar(1) NOT NULL default 'N',
  `m_cellphone` varchar(1) NOT NULL default 'N',
  `m_csamemberships` int(11) NOT NULL default '0',
  `m_csaweeks` int(11) NOT NULL default '0',
  `m_foodseating` varchar(1) NOT NULL default 'N',
  `m_summaryholding` text,
  `m_longsummaryholding` text,
  `m_photoholding` varchar(250) default NULL,
  `m_approved` varchar(1) NOT NULL default 'N',
  `m_startdate` date NOT NULL,
  `sub_id` int(11) NOT NULL,
  PRIMARY KEY  (`memberid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

The desired result is a complete list of categories under a parent category with the member_id either set to the member's ID or NULL (so I can display a checkbox list with the categories the member has preselected)

 

The data should look like:

c_name

catid

memberid

lft

rgt

Bathroom Accommodations49353758

Bed & Breakfast479NULL 910

Bunkhouse 481NULL1314

Cabins482NULL1516

Conestoga Wagons 740NULL8788

 

`tblCategory` has ~1000 entries

`tblMember` has ~3500 entries

`jtMemberCategory` has ~245,000 entries

 

The SQL I am using:

SELECT c_name, catid, memberid, lft,rgt 
FROM (tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5) 
  left JOIN tblMember ON member_id=memberid and memberid=5 
WHERE tblCategory.cat_id=25 
ORDER BY c_name

 

Some of the stuff I've tried - removing the second left join:

SELECT c_name, catid, member_id, lft,rgt 
FROM tblCategory left JOIN jtMemberCategory ON catid=jtMemberCategory.cat_id AND member_id=5 
WHERE tblCategory.cat_id=25 
ORDER BY c_name

Same time result. (and the same data returned, so I'm made it a little less complex)

 

I added DISTINCT (which wasn't an issue with this version of SQL, but was with some of the versions I've tried)

 

The member_id in the column list isn't a field that has to contain meaningful information, just a NULL and something else to indicate that the left join was a match.

 

I could do this in PHP with a loop and just pull the categories out and match later on (maybe 50 sub categories per parent, so we're talking 50 loops x the number of matches in the subcategory for the member, not very efficient, imo)

 

mysql version - 5.0.45-community-log

 

I don't know how to make my SQL more efficient, any assistance would be appreciated.

Thank you

[AP81]

Firstly:

Table jtMemberCategory has no primary key.  If member_id and cat_id together make a unique key, the create a composite key on these columns.

 

Secondly:

Use the EXPLAIN MySQL function.  Make this a habit!  This will help you to optimise you joins and find bottlenecks

 

Thirdly:

I would change your query to this:

SELECT c_name, tblmember.catid, tblmember.memberid, tblCategory.lft, tblCategory.rgt 
FROM (tblCategory left JOIN jtMemberCategory ON tblCategory.catid=jtMemberCategory.cat_id)
  left JOIN tblMember ON tblMember.member_id=jtMemberCategory.memberid 
WHERE tblCategory.cat_id=25
AND jtMemberCategory.member_id=5
ORDER BY c_name

 

Because jtMemberCategory has 245,000 rows, specifying jtMemberCategory.member_id=5 cuts this table down a lot.  Your joins in theory will speed up.

 

I am guessing that you are using MySQL control center, as I can recall having a similar problem.  Make sure you use the latest version will fix this problem.

 

[kaosweaver]

I'm sorry, I didn't explain well enough what I wanted, the query you provided I needed to modify (typos) to:

 

SELECT c_name, tblCategory.catid, jtMemberCategory.member_id, tblCategory.lft, tblCategory.rgt

FROM (tblCategory left JOIN jtMemberCategory ON tblCategory.catid=jtMemberCategory.cat_id)

  left JOIN tblMember ON tblMember.memberid=jtMemberCategory.member_id

WHERE tblCategory.cat_id=200

AND jtMemberCategory.member_id=5

ORDER BY c_name

 

and this only returns the categories the member has, not all of the categories with a NULL in the spot for the member_id.

[AP81]

Well your join is around the wrong way...you want to join tblCategory to jtMemberCategory not the the other way around:

 

SELECT c_name, tblCategory.catid, jtMemberCategory.member_id, tblCategory.lft, tblCategory.rgt
FROM (jtMemberCategory left JOIN tblCategory ON jtMemberCategory.cat_id = tblCategory.catid)
  left JOIN tblMember ON tblMember.memberid=jtMemberCategory.member_id
WHERE tblCategory.cat_id=200
AND jtMemberCategory.member_id=5
ORDER BY c_name