MySQL Forums :: PHP :: select with join of 25 tables crashes mysql database

I have created an e-learning application at
http://www.thewordmap.com/topic.php
The application contains 4,200 words and records which words users are able to match with phrases. One table "modl_app" contains all of the words and phrases. The words are grouped into 24 topics. There is a table for each topic in which the matches are recorded. I can join all 24 topic tables with the modl_app table and display all the matches that one user has made.

However, I need to be able display the matches made by all of the users. In this query I don't need to include the modl_app table. I join the topic tables with a modl_user table that contains all of my users. I can do this with 12 of the tables, but the speed of the query begins to slow down as I add more tables. Trying to join 24 topic tables with the the modl_user table crashes my database. I use a web hosting service.

This is my query for joining 13 tables:

$query1="SELECT * FROM modl_user INNER JOIN (modl_00, modl_01, modl_02, 
modl_03, modl_04, modl_05, modl_06, modl_07, modl_08, modl_09, modl_10,  
modl_11) ON (modl_00.userid=modl_user.id AND modl_01.userid=modl_user.id AND
 modl_02.userid=modl_user.id AND modl_03.userid=modl_user.id AND 
modl_04.userid=modl_user.id AND modl_05.userid=modl_user.id AND 
modl_06.userid=modl_user.id AND modl_07.userid=modl_user.id AND 
modl_08.userid=modl_user.id AND modl_09.userid=modl_user.id AND 
modl_10.userid=modl_user.id AND modl_11.userid=modl_user.id)";

This is my query for joining 25 tables:

$query0="SELECT * FROM modl_user INNER JOIN (modl_00, modl_01, modl_02, 
modl_03, modl_04, modl_05, modl_06, modl_07, modl_08, modl_09, modl_10,  
modl_11, modl_12, modl_13, modl_14, modl_15, modl_16, modl_17, modl_18, 
modl_19, modl_20, modl_21, modl_22,  modl_23) ON 
(modl_00.userid=modl_user.id AND modl_01.userid=modl_user.id AND 
modl_02.userid=modl_user.id AND modl_03.userid=modl_user.id AND 
modl_04.userid=modl_user.id AND modl_05.userid=modl_user.id AND 
modl_06.userid=modl_user.id AND modl_07.userid=modl_user.id AND 
modl_08.userid=modl_user.id AND modl_09.userid=modl_user.id AND 
modl_10.userid=modl_user.id AND modl_11.userid=modl_user.id AND 
modl_12.userid=modl_user.id AND modl_13.userid=modl_user.id AND 
modl_14.userid=modl_user.id AND modl_15.userid=modl_user.id AND 
modl_16.userid=modl_user.id AND modl_17.userid=modl_user.id AND 
modl_18.userid=modl_user.id AND modl_19.userid=modl_user.id AND 
modl_20.userid=modl_user.id AND modl_21.userid=modl_user.id AND 
modl_22.userid=modl_user.id AND modl_23.userid=modl_user.id)";

The modl_user table is part of the Moodle Course Management System. The structure is as follows.

CREATE TABLE `modl_user` (
  `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
  `auth` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'manual',
  `confirmed` tinyint(1) NOT NULL DEFAULT '0',
  `policyagreed` tinyint(1) NOT NULL DEFAULT '0',
  `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `suspended` tinyint(1) NOT NULL DEFAULT '0',
  `mnethostid` bigint(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `password` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `idnumber` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `emailstop` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `icq` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `skype` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `yahoo` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `aim` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `msn` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `phone1` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `phone2` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `institution` varchar(40) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `department` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `address` varchar(70) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `city` varchar(120) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `country` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `lang` varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
  `theme` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `timezone` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '99',
  `firstaccess` bigint(10) unsigned NOT NULL DEFAULT '0',
  `lastaccess` bigint(10) unsigned NOT NULL DEFAULT '0',
  `lastlogin` bigint(10) unsigned NOT NULL DEFAULT '0',
  `currentlogin` bigint(10) unsigned NOT NULL DEFAULT '0',
  `lastip` varchar(45) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `secret` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `picture` tinyint(1) NOT NULL DEFAULT '0',
  `url` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `description` longtext COLLATE utf8_unicode_ci,
  `descriptionformat` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `mailformat` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `maildigest` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `maildisplay` tinyint(2) unsigned NOT NULL DEFAULT '2',
  `htmleditor` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `ajax` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `autosubscribe` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `trackforums` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `timecreated` bigint(10) unsigned NOT NULL DEFAULT '0',
  `timemodified` bigint(10) unsigned NOT NULL DEFAULT '0',
  `trustbitmask` bigint(10) unsigned NOT NULL DEFAULT '0',
  `imagealt` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `screenreader` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `modl_user_mneuse_uix` (`mnethostid`,`username`),
  KEY `modl_user_del_ix` (`deleted`),
  KEY `modl_user_con_ix` (`confirmed`),
  KEY `modl_user_fir_ix` (`firstname`),
  KEY `modl_user_las_ix` (`lastname`),
  KEY `modl_user_cit_ix` (`city`),
  KEY `modl_user_cou_ix` (`country`),
  KEY `modl_user_las2_ix` (`lastaccess`),
  KEY `modl_user_ema_ix` (`email`),
  KEY `modl_user_aut_ix` (`auth`),
  KEY `modl_user_idn_ix` (`idnumber`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='One record for each person';
/*!40101 SET character_set_client = @saved_cs_client */;

An example of a topic table is shown below. All of the topic tables have the same structure. They each have 181 fields which correspond to the values of a wordid field in the modl_app table. For example, the first word in topic 14 has a wordid of 'w14000', and in topic 15 the first word has a wordid of 'w15000'.

CREATE TABLE IF NOT EXISTS `modl_14` (
  `id` tinyint(6) NOT NULL auto_increment,
  `userid` tinyint(6) NOT NULL,
  `t14` tinyint(1) default 0,
  `w14000` tinyint(1) default 0,
  `w14002` tinyint(1) default 0,
  `w14004` tinyint(1) default 0,
  `w14010` tinyint(1) default 0,
  `w14012` tinyint(1) default 0,
  `w14014` tinyint(1) default 0,
  `w14020` tinyint(1) default 0,
  `w14022` tinyint(1) default 0,
  `w14024` tinyint(1) default 0,
  `w14030` tinyint(1) default 0,
  `w14032` tinyint(1) default 0,
  `w14034` tinyint(1) default 0,
  `w14040` tinyint(1) default 0,
  `w14042` tinyint(1) default 0,
  `w14044` tinyint(1) default 0,
  `w14050` tinyint(1) default 0,
  `w14052` tinyint(1) default 0,
  `w14054` tinyint(1) default 0,
  `w14060` tinyint(1) default 0,
  `w14062` tinyint(1) default 0,
  `w14064` tinyint(1) default 0,
  `w14070` tinyint(1) default 0,
  `w14072` tinyint(1) default 0,
  `w14074` tinyint(1) default 0,
  `w14080` tinyint(1) default 0,
  `w14082` tinyint(1) default 0,
  `w14084` tinyint(1) default 0,
  `w14090` tinyint(1) default 0,
  `w14092` tinyint(1) default 0,
  `w14094` tinyint(1) default 0,
  `w14100` tinyint(1) default 0,
  `w14102` tinyint(1) default 0,
  `w14104` tinyint(1) default 0,
  `w14110` tinyint(1) default 0,
  `w14112` tinyint(1) default 0,
  `w14114` tinyint(1) default 0,
  `w14120` tinyint(1) default 0,
  `w14122` tinyint(1) default 0,
  `w14124` tinyint(1) default 0,
  `w14130` tinyint(1) default 0,
  `w14132` tinyint(1) default 0,
  `w14134` tinyint(1) default 0,
  `w14140` tinyint(1) default 0,
  `w14142` tinyint(1) default 0,
  `w14144` tinyint(1) default 0,
  `w14150` tinyint(1) default 0,
  `w14152` tinyint(1) default 0,
  `w14154` tinyint(1) default 0,
  `w14160` tinyint(1) default 0,
  `w14162` tinyint(1) default 0,
  `w14164` tinyint(1) default 0,
  `w14170` tinyint(1) default 0,
  `w14172` tinyint(1) default 0,
  `w14174` tinyint(1) default 0,
  `w14180` tinyint(1) default 0,
  `w14182` tinyint(1) default 0,
  `w14184` tinyint(1) default 0,
  `w14190` tinyint(1) default 0,
  `w14192` tinyint(1) default 0,
  `w14194` tinyint(1) default 0,
  `w14200` tinyint(1) default 0,
  `w14202` tinyint(1) default 0,
  `w14204` tinyint(1) default 0,
  `w14210` tinyint(1) default 0,
  `w14212` tinyint(1) default 0,
  `w14214` tinyint(1) default 0,
  `w14220` tinyint(1) default 0,
  `w14222` tinyint(1) default 0,
  `w14224` tinyint(1) default 0,
  `w14230` tinyint(1) default 0,
  `w14232` tinyint(1) default 0,
  `w14234` tinyint(1) default 0,
  `w14240` tinyint(1) default 0,
  `w14242` tinyint(1) default 0,
  `w14244` tinyint(1) default 0,
  `w14250` tinyint(1) default 0,
  `w14252` tinyint(1) default 0,
  `w14254` tinyint(1) default 0,
  `w14260` tinyint(1) default 0,
  `w14262` tinyint(1) default 0,
  `w14264` tinyint(1) default 0,
  `w14270` tinyint(1) default 0,
  `w14272` tinyint(1) default 0,
  `w14274` tinyint(1) default 0,
  `w14280` tinyint(1) default 0,
  `w14282` tinyint(1) default 0,
  `w14284` tinyint(1) default 0,
  `w14290` tinyint(1) default 0,
  `w14292` tinyint(1) default 0,
  `w14294` tinyint(1) default 0,
  `w14300` tinyint(1) default 0,
  `w14302` tinyint(1) default 0,
  `w14304` tinyint(1) default 0,
  `w14310` tinyint(1) default 0,
  `w14312` tinyint(1) default 0,
  `w14314` tinyint(1) default 0,
  `w14320` tinyint(1) default 0,
  `w14322` tinyint(1) default 0,
  `w14324` tinyint(1) default 0,
  `w14330` tinyint(1) default 0,
  `w14332` tinyint(1) default 0,
  `w14334` tinyint(1) default 0,
  `w14340` tinyint(1) default 0,
  `w14342` tinyint(1) default 0,
  `w14344` tinyint(1) default 0,
  `w14350` tinyint(1) default 0,
  `w14352` tinyint(1) default 0,
  `w14354` tinyint(1) default 0,
  `w14360` tinyint(1) default 0,
  `w14362` tinyint(1) default 0,
  `w14364` tinyint(1) default 0,
  `w14370` tinyint(1) default 0,
  `w14372` tinyint(1) default 0,
  `w14374` tinyint(1) default 0,
  `w14380` tinyint(1) default 0,
  `w14382` tinyint(1) default 0,
  `w14384` tinyint(1) default 0,
  `w14390` tinyint(1) default 0,
  `w14392` tinyint(1) default 0,
  `w14394` tinyint(1) default 0,
  `w14400` tinyint(1) default 0,
  `w14402` tinyint(1) default 0,
  `w14404` tinyint(1) default 0,
  `w14410` tinyint(1) default 0,
  `w14412` tinyint(1) default 0,
  `w14414` tinyint(1) default 0,
  `w14420` tinyint(1) default 0,
  `w14422` tinyint(1) default 0,
  `w14424` tinyint(1) default 0,
  `w14430` tinyint(1) default 0,
  `w14432` tinyint(1) default 0,
  `w14434` tinyint(1) default 0,
  `w14440` tinyint(1) default 0,
  `w14442` tinyint(1) default 0,
  `w14444` tinyint(1) default 0,
  `w14450` tinyint(1) default 0,
  `w14452` tinyint(1) default 0,
  `w14454` tinyint(1) default 0,
  `w14460` tinyint(1) default 0,
  `w14462` tinyint(1) default 0,
  `w14464` tinyint(1) default 0,
  `w14470` tinyint(1) default 0,
  `w14472` tinyint(1) default 0,
  `w14474` tinyint(1) default 0,
  `w14480` tinyint(1) default 0,
  `w14482` tinyint(1) default 0,
  `w14484` tinyint(1) default 0,
  `w14490` tinyint(1) default 0,
  `w14492` tinyint(1) default 0,
  `w14494` tinyint(1) default 0,
  `w14500` tinyint(1) default 0,
  `w14502` tinyint(1) default 0,
  `w14504` tinyint(1) default 0,
  `w14510` tinyint(1) default 0,
  `w14512` tinyint(1) default 0,
  `w14514` tinyint(1) default 0,
  `w14520` tinyint(1) default 0,
  `w14522` tinyint(1) default 0,
  `w14524` tinyint(1) default 0,
  `w14530` tinyint(1) default 0,
  `w14532` tinyint(1) default 0,
  `w14534` tinyint(1) default 0,
  `w14540` tinyint(1) default 0,
  `w14542` tinyint(1) default 0,
  `w14544` tinyint(1) default 0,
  `w14550` tinyint(1) default 0,
  `w14552` tinyint(1) default 0,
  `w14554` tinyint(1) default 0,
  `w14560` tinyint(1) default 0,
  `w14562` tinyint(1) default 0,
  `w14564` tinyint(1) default 0,
  `w14570` tinyint(1) default 0,
  `w14572` tinyint(1) default 0,
  `w14574` tinyint(1) default 0,
  `w14580` tinyint(1) default 0,
  `w14582` tinyint(1) default 0,
  `w14584` tinyint(1) default 0,
  `w14590` tinyint(1) default 0,
  `w14592` tinyint(1) default 0,
  `w14594` tinyint(1) default 0,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `userid` (`userid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Ultimately I want to create a page that reports how many students in a specific group have matched the words in a specific homework assignment. In which case, I expect I will add three additional tables to the select query, i.e. Moodle's modl_groups and modl_groups_members tables and my own modl_homework table. However, I cannot accomplish this until I have resolved the problem with the query which crashes the database.

I suspect my problem is related to the limitations of memory in a single query record, but I have no problem retrieving a single record in the following query:

$query="SELECT * FROM modl_app, modl_00,  modl_01,  modl_02,  modl_03, 
modl_04,  modl_05,  modl_06, modl_07, modl_08, modl_09, modl_10,  modl_11, 
 modl_12,  modl_13,  modl_14,  modl_15,  modl_16,  modl_17,  modl_18,  
modl_19,  modl_20,  modl_21,  modl_22,  modl_23  WHERE  
modl_00.userid='$student' AND modl_01.userid='$student' AND 
modl_02.userid='$student' AND modl_03.userid='$student' AND 
modl_04.userid='$student' AND modl_05.userid='$student' AND 
modl_06.userid='$student' AND modl_07.userid='$student' AND 
modl_08.userid='$student' AND modl_09.userid='$student' AND 
modl_10.userid='$student' AND modl_11.userid='$student' AND 
modl_12.userid='$student' AND modl_13.userid='$student' AND 
modl_14.userid='$student' AND modl_15.userid='$student' AND 
modl_16.userid='$student' AND modl_17.userid='$student' AND 
modl_18.userid='$student' AND modl_19.userid='$student' AND 
modl_20.userid='$student' AND modl_21.userid='$student' AND 
modl_22.userid='$student' AND modl_23.userid='$student' ORDER BY 
modl_app.topic, modl_app.win";

Thanks for reading this far. Any suggestions will be greatly appreciated.



Edited 1 time(s). Last edit at 10/04/2012 01:08AM by Ted OBrien.