MySQL Forums
Forum List  »  Optimizer & Parser

Re: want to make this query faster, but not sure how
Posted by: Luc Foisy
Date: January 20, 2012 10:51AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: want to make this query faster, but not sure how
1205
January 20, 2012 10:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.