I lease server space from an ISP and don't have access to any configuration files, but Almar's reply led me to discover that using SET SQL_BIG_TABLES=1 before the query turned the trick.
Thanks for the INNER JOIN tip, but evidently version 3.22.32 doesn't support it. Pity, anything to speed the query would be very helpful. To that end, could I impose on someone to look at the output from the EXPLAIN and DESC's below and tell me how to index my tables for faster queries? Are there any negatives involved in indexing? EXPLAIN Select * from Players,Roster where Roster.playerId=Players.id order by Players.plast limit 1,10 +---------+------+---------------+------+---------+------+------+----------- -----------------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+------+---------------+------+---------+------+------+----------- -----------------------------------+ | Roster | ALL | NULL | NULL | NULL | NULL | 1863 | | | Players | ALL | PRIMARY,id | NULL | NULL | NULL | 1775 | range checked for each record (index map: 3) | +---------+------+---------------+------+---------+------+------+----------- -----------------------------------+ 2 rows in set (0.00 sec) mysql> desc Roster; +------------+------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+----------------+ | id | int(6) | | PRI | 0 | auto_increment | | playerId | int(6) | | | 0 | | | teamId | int(6) | YES | | NULL | | | teamAge | char(5) | YES | | 0 | | | seasonYear | int(4) | | | 0 | | | season | tinyint(1) | | | 0 | | +------------+------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> desc Players; +-------------------+--------------+------+-----+------------+-------------- --+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+------------+-------------- --+ | id | smallint(6) | | PRI | 0 | auto_increment | | dob | date | YES | | NULL | | | plast | varchar(40) | YES | | NULL | | | pfirst | varchar(40) | YES | | NULL | | | pmiddle | char(1) | YES | | NULL | | | pstreet | varchar(80) | YES | | NULL | | | pcity | varchar(30) | YES | | NULL | | | pzip | varchar(10) | YES | | NULL | | | pac | char(3) | YES | | NULL | | | pexchange | char(3) | YES | | NULL | | | pphone | varchar(4) | YES | | NULL | | | flast | varchar(30) | YES | | NULL | | | ffirst | varchar(30) | YES | | NULL | | | fac | char(3) | YES | | NULL | | | fexchange | char(3) | YES | | NULL | | | fphone | varchar(4) | YES | | NULL | | | mlast | varchar(30) | YES | | NULL | | | mfirst | varchar(30) | YES | | NULL | | | mac | char(3) | YES | | NULL | | | mexchange | char(3) | YES | | NULL | | | mphone | varchar(4) | YES | | NULL | | | elast | varchar(30) | YES | | NULL | | | efirst | varchar(30) | YES | | NULL | | | eac | char(3) | YES | | NULL | | | eexchange | char(3) | YES | | NULL | | | ephone | varchar(4) | YES | | NULL | | | teamAssigned | int(4) | YES | | NULL | | | dateAssigned | date | YES | | NULL | | | jersey | int(3) | YES | | NULL | | | pos1 | char(3) | YES | | NULL | | | pos2 | char(3) | YES | | NULL | | | pos3 | char(3) | YES | | NULL | | | bats | char(1) | YES | | NULL | | | throws | char(1) | YES | | NULL | | | ht | int(2) | YES | | NULL | | | wt | int(2) | YES | | NULL | | | e_mail | varchar(80) | YES | | NULL | | | experience | int(2) | YES | | NULL | | | nearestElementary | varchar(40) | | | | | | registrationDate | date | | | 0000-00-00 | | | fextension | int(4) | | | 0 | | | mextension | int(4) | | | 0 | | | eextension | int(4) | | | 0 | | | yearstamp | int(4) | | | 0 | | | seasonstamp | varchar(10) | | | | | | ageGroup | tinyint(2) | YES | | NULL | | | comment | varchar(255) | YES | | NULL | | | seasonPlayed | varchar(255) | YES | | NULL | | +-------------------+--------------+------+-----+------------+-------------- --+ 48 rows in set (0.00 sec) -----Original Message----- From: Almar van Pel [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 13, 2001 9:33 AM To: Mayo, Chuck Cc: [EMAIL PROTECTED] Subject: RE: JOIN and Table Full error Hi, Your setting tmp_table_size is probably very low. (something like 1048576) Try increasing your value to 2M or 4M, and you might want to increase your key_buffer as wel to 16M. I would also like to advise you to rephrase your query to something that looks like: select * from players inner join on roster Roster.playerId=Players.id order by player.plast limit 1,10 or use a left join. The above query syntax uses less memory. >> select * from Players,Roster where Roster.playerId=Players.id order by >> Players.plast limit 1,10 Regards, Almar van Pel -----Oorspronkelijk bericht----- Van: Mayo, Chuck [mailto:[EMAIL PROTECTED]] Verzonden: donderdag, december 13, 2001 04.11 Aan: '[EMAIL PROTECTED]' Onderwerp: JOIN and Table Full error Hi all, I'm pretty new with MySQL and am trying to implement my first join. All works as expected until I try to order the output with an "order by" clause; select * from Players,Roster where Roster.playerId=Players.id order by Players.plast limit 1,10 and I receive an error from the MySQL interpreter; ERROR 1114: The table 'SQLe1b02_0' is full I have no tables by that name, must be a temp? There seems to be ample space in my filesystem, can someone tell me why this is happening and, even better, how to fix it? --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
