4 messages in com.mysql.lists.clusterRE: ndb unique index performance issues| From | Sent On | Attachments |
|---|---|---|
| Yong Lee | 29 Jun 2006 16:01 | |
| Benton, Kevin | 29 Jun 2006 16:42 | |
| Simon Garner | 29 Jun 2006 17:40 | |
| Stewart Smith | 29 Jun 2006 22:29 |
| Subject: | RE: ndb unique index performance issues |
|---|---|
| From: | Benton, Kevin (kevi...@amd.com) |
| Date: | 06/29/2006 04:42:15 PM |
| List: | com.mysql.lists.cluster |
I'm trying to do a query that uses an index along with a unique index and for some reason, the query seems to be doing a table scan. My table has 20 000+ records and my query is taking over 20 seconds to return. Any insight into this problem would be appreciated. Here are the details :
mysql 4.1.12, 2 partitions with 2 servers each hosting a data and sql node:
The explain results tell all...
Do you see in your explain statements, where one says type: range and the other says type: ref? For whatever reason, MySQL chose to use some other way to locate your data. As a result, MySQL created a temporary table to grab what you were asking it for.
You may want to try reversing the order of your comparisons in your select statement.
Since username is a unique key, it seems to me that username is a better primary key to use than id, unless you're using id to save storage space in other tables. Optimization tip - don't use id's for things that are already by themselves, unique and easily indexed.
--- Kevin Benton Perl/Bugzilla Developer/Administrator, Perforce SCM Administrator AMD - ECSD Software Validation and Tools
The opinions stated in this communication do not necessarily reflect the view of Advanced Micro Devices and have not been reviewed by management. This communication may contain sensitive and/or confidential and/or proprietary information. Distribution of such information is strictly prohibited without prior consent of Advanced Micro Devices. This communication is for the intended recipient(s) only. If you have received this communication in error, please notify the sender, then destroy any remaining copies of this communication.