>>>>> "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
