This will really speed things up:
ALTER TABLE _ISO ADD KEY(_objective_id)
If you do an EXPLAIN on your current query, you would see that no index is
used for the _iso table so the engine has to do a table scan. And, for
future reference, the output of SHOW CREATE TABLES is more informative
than DESCRIBE because it identifies which columns are in which index and
in what order. Don't forget to use \G and not ; as your command terminator
(like this:
SHOW CREATE TABLE _iso\G
or you will end up with lots of extra formatting)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Mark Worsdall <[EMAIL PROTECTED]> wrote on 11/11/2004 03:56:12 PM:
>
>
>
> SELECT
> _objectives.id,
> _objectives.name,
> COUNT(go._iso._objective_id)
> FROM go._objectives
> INNER JOIN go._subjectHeadings
> ON go._subjectHeadings.id = go._objectives.subjectHeadings_id
> INNER JOIN go._subjects
> ON go._subjects.id = go._objectives.subjects_id
> LEFT JOIN go._iso
> ON go._iso._objective_id = _objectives.id
> WHERE
> go._subjectHeadings.id = 276
> AND
> go._subjects.id = 44
> GROUP BY 1,2
> ORDER BY go._objectives.displayOrder
>
> 58 rows in set (24.26 sec)
>
>
> Did what I wanted but took as long as old method.
>
> the _iso.user_id value for this is 175 in case that can be used to speed
> things up.
>
> I have tried various things but they all end up with the servers hard
> drive going nutty and /tmp (1Gb) filling up!!!
>
>
> I have included the descriptions of the tables and a count on them to
> show number of records involved.
>
> I can't see the solution yet but I am sure it can be done with the
> select without me delving into indexes etc.
>
>
> describe _objectives;
> +--------------------+---------------+------+-----+---------+
> | Field | Type | Null | Key | Default |
> +--------------------+---------------+------+-----+---------+
> | id | int(10) | | PRI | NULL |
> | subjects_id | int(10) | YES | | NULL |
> | subjectHeadings_id | int(10) | YES | | NULL |
> | name | varchar(240) | | | |
> | active | enum('Y','N') | | | Y |
> | displayOrder | int(10) | | | 10 |
> | owner_id | int(10) | | | 0 |
> +--------------------+---------------+------+-----+---------+
> SELECT count(*) from go._objectives;
> +----------+
> | count(*) |
> +----------+
> | 4087 |
> +----------+
> 1 row in set (0.01 sec)
>
>
> describe _iso;
> +---------------+---------------+------+-----+---------------------+
> | Field | Type | Null | Key | Default |
> +---------------+---------------+------+-----+---------------------+
> | id | int(10) | | PRI | NULL |
> | student_id | int(10) | | | 0 |
> | ltaForm_id | int(10) | | | 0 |
> | lta_id | int(10) | | | 0 |
> | _objective_id | int(10) | | | 0 |
> | sta | varchar(254) | | | |
> | comment | varchar(254) | | | |
> | lecturerNotes | varchar(254) | | | |
> | displayOrder | int(10) | | | 100 |
> | manstatus | enum('W','C') | | | W |
> | autostatus | enum('W','C') | | | W |
> | recType | enum('T','L') | | | T |
> | active | enum('Y','N') | | | Y |
> | create_date | datetime | | | 2003-12-08 00:00:00 |
> | modified_date | timestamp(14) | YES | | NULL |
> | moduser_id | int(10) | | | 0 |
> | user_id | int(10) | | | 0 |
> | recuser_id | int(10) | | | 0 |
> | rec_date | datetime | | | 2003-12-08 00:00:00 |
> +---------------+---------------+------+-----+---------------------+
> SELECT count(*) from go._iso;
> +----------+
> | count(*) |
> +----------+
> | 11498 |
> +----------+
> 1 row in set (0.01 sec)
>
>
>
> This was on the end of both describe results.
> ----------------+
> Extra |
> ----------------+
> auto_increment |
> |
> |
> |
> |
> |
> |
> ----------------+
>
>
>
>
>
>
>
>
> --
> Work:- postmasterAThinwick.demon.co.uk
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
