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.