Re: want to make this query faster, but not sure how

[Luc Foisy]

to tell you the truth, I don't know why the indexes were made like that, I wasn't involved in the original creation

so I did the following
ALTER TABLE `member` DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`);
ALTER TABLE `member` ADD KEY (`membershipID`);
ALTER TABLE `member` ADD UNIQUE KEY (`webUserID`);

ALTER TABLE `company_webuser` DROP PRIMARY KEY, ADD PRIMARY KEY (`ID`);
ALTER TABLE `company_webuser` ADD KEY (`webuserID`);
ALTER TABLE `company_webuser` ADD KEY (`companyID`);

and it's now super fast, thanks a bunch!

irek kordirko Wrote:
-------------------------------------------------------
> Hi,
>
> Are these 3 columns really a primary key of the
> 'member' table ?
> Or maybe should be 'ID' alone, since it is
> auto-incremented ?
> Could you throw some light on this ?
>
> CREATE TABLE `member` (
> `ID` int(11) NOT NULL auto_increment,
> ......
> PRIMARY KEY (`ID`,`membershipID`,`webUserID`)
>
> If no, alter your table and make 'ID' column as
> primary key.
>
>
> Whether you change PK or not, create an index on
> 'webUserID',
> since your query joins the 'member' table using
> this condition:
> LEFT JOIN `member` ON `webusers`.`webuserID` =
> `member`.`webUserID`
>
> Also please use code tags [ code ] + [ /code ],
> this will make your post more readable, this is an
> example how it works:
>
>
> +----+-------------+-----------------+--------+---
> ------------+---------+---------+-----------------
> ---------------+------+---------------------------
> -------------------+
> | id | select_type | table | type |
> possible_keys | key | key_len | ref
> | rows | Extra
> |
> +----+-------------+-----------------+--------+---
> ------------+---------+---------+-----------------
> ---------------+------+---------------------------
> -------------------+
> | 1 | SIMPLE | webusers | range |
> status | status | 4 | NULL
> | 1544 | Using where; Using
> temporary; Using filesort |
> | 1 | SIMPLE | company_webuser | index |
> NULL | PRIMARY | 12 | NULL
> | 264 | Using index
> |
> | 1 | SIMPLE | company | eq_ref |
> PRIMARY | PRIMARY | 4 |
> icsa.company_webuser.companyID | 1 |
> |
>