MySQL Forums
Forum List  »  Optimizer & Parser

Re: want to make this query faster, but not sure how
Posted by: irek kordirko
Date: January 19, 2012 05:39PM

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



Edited 2 time(s). Last edit at 01/19/2012 05:41PM by irek kordirko.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: want to make this query faster, but not sure how
1247
January 19, 2012 05:39PM


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.