MySQL Forums
Forum List  »  PHP

select with join of 25 tables crashes mysql database
Posted by: Ted OBrien
Date: October 04, 2012 12:47AM

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.

Options: ReplyQuote




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.