List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 10:42pm
Subject:Re: temp tables in memory vs. disk
View as plain text  
>>>>> "Cooper" == Cooper Vertz <cooper@stripped> writes:

Cooper> Monty,

Cooper> I am having a similar problem.  Do you have any suggestions for
Cooper> optimizing this query?  It was fairly quick when the two tables were
Cooper> small, but now it takes up to 4 minutes using 3.22.26a/pc-linux-gnu on
Cooper> a PII-450.  Most of the time is spent "Copying to tmp table", with 99%
Cooper> cpu activity but very little disk activity.

Cooper> select member.username as 'User', member.name as 'Name',
Cooper>  member.plan as 'Plan', ifnull(member.company, 'n/a') as 'Company',
Cooper>  count(stats.count) as 'Total Visits', max(stats.countdate) as 'Last Visit',
Cooper>  member.end as 'Expires', member.email as Mailto from member
Cooper>  left join stats on (member.username=stats.value)
Cooper>  where ifnull(stats.name,'user') = 'user'
Cooper>  group by 1 order by member.plan, member.company, member.name

Cooper> Based on your advice to others, I have already used isamchk -a on both
Cooper> tables, but that didn't have any effect.  Also, I am not able to find
Cooper> the SQL-* temporary files in /tmp or /var/tmp, which seems odd.

Cooper> Here is the output of explain:

Cooper> +--------+------+---------------+------+---------+------+-------+------------+
Cooper> | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra      |
Cooper> +--------+------+---------------+------+---------+------+-------+------------+
Cooper> | member | ALL  | NULL          | NULL |    NULL | NULL |  1105 |            |
Cooper> | stats  | ALL  | value         | NULL |    NULL | NULL | 54834 | where used |
Cooper> +--------+------+---------------+------+---------+------+-------+------------+
Cooper> 2 rows in set (0.29 sec)

AS you can see, MySQL is not using any indexes.

My guess is that 'member.username' doesn't have the same column
definition as 'stats.value'.  If you fix this, the query will be MUCH
faster!

If you can't fix this (with ALTER TABLE), the other option is to use
MySQL 3.23.


<cut>

Cooper> Here's the create table syntax for the two tables:

Cooper> CREATE TABLE stats (
Cooper>   countdate date DEFAULT '0000-00-00' NOT NULL,
Cooper>   name char(8) DEFAULT '' NOT NULL,
Cooper>   value char(48) DEFAULT '' NOT NULL,
          ^^^^^^^^^^^^^^
Cooper>   count bigint(21) DEFAULT '0' NOT NULL,
Cooper>   KEY name (name),
Cooper>   KEY value (value(16)),
Cooper>   PRIMARY KEY (countdate,name,value)
Cooper> );

Cooper> CREATE TABLE member (
Cooper>   username char(32) binary DEFAULT '' NOT NULL,
                   ^^^^^^^^^^^^^^^

Cooper>   plan int(11) DEFAULT '0' NOT NULL,
Cooper>   password char(32) binary,
Cooper>   name char(80),
Cooper>   company char(80),
Cooper>   addr1 char(80),
Cooper>   addr2 char(80),
Cooper>   city char(80),
Cooper>   state char(20),
Cooper>   zip char(20),
Cooper>   country char(80),
Cooper>   email char(80),
Cooper>   start date,
Cooper>   end date,
Cooper>   monthly float(10,2),
Cooper>   lastbilled date,
Cooper>   phone char(20),
Cooper>   billcycle int(11),
Cooper>   purchaseid char(20),
Cooper>   acct_code char(20),
Cooper>   PRIMARY KEY (username),
Cooper>   KEY plan (plan)
Cooper> );

Regards,
Monty
Thread
temp tables in memory vs. disk(David Sklar)27 Sep
  • Re: temp tables in memory vs. diskRick Moore27 Sep
    • Re: temp tables in memory vs. diskMichael Widenius29 Sep
  • Re: temp tables in memory vs. diskCooper Vertz12 Oct
    • Re: temp tables in memory vs. diskMichael Widenius13 Oct
  • Re: temp tables in memory vs. diskCooper Vertz13 Oct
  • Re: temp tables in memory vs. diskCooper Vertz16 Oct
    • Re: temp tables in memory vs. disksinisa16 Oct