List:General Discussion« Previous MessageNext Message »
From:Gordon Date:April 27 2006 4:44pm
Subject:Re: Multiple primary keys
View as plain text  
MySQL will not use the primary key unless you use the left most columns. For 
a 1 column primary key then it's easy. For a 2 column primary key you can 
either use the 1st column in the primary key or both columns. If you only 
reference the 2nd column the query will not use the primary key and will do 
a full table scan.

In your case you are referencing "classb" which is not the left most collumn 
in the primary key set. Put the word "explain" preceding  the statement and 
execute the query. it will show you what keys are used in the query and in 
your case it is none. You either need to define another KEY with classb as 
the 1st column in the definition or if all of your queries at least 
reference classb then you could rebuild the primary key and put classb as 
the 1st entry in the definition.
----- Original Message ----- 
From: "nngau" <nngau@stripped>
To: "'Kishore Jalleda'" <kjalleda@stripped>
Cc: <mysql@stripped>
Sent: Thursday, April 27, 2006 9:11 AM
Subject: RE: Multiple primary keys


> Thanks all. The query I run is a subquery.
>
> I noticed joined query run a lot faster than the sub.
>
> This is the subquery:
> select * from class_c where detail_id in (select classC from
> item_classification where classb="216") order by detail;
>
> This query takes nearly 3 minutes, before it did not take that long. I 
> guess
> I should use a primary key As an index.
>
> I want to be able to add items that I can classify into different classa,
> classb or classc.
>
> Example:
>
> Itemid 1025 ClassA: 101 classB: 218 classC: 356
>
> Same item can be put into another class.
>
> Itemid 105 ClassA: 101 classb: 218 classC: 357
>
> So not having a primary key/index will slow my queries?
>
>
> -----Original Message-----
> From: Kishore Jalleda [mailto:kjalleda@stripped]
> Sent: Thursday, April 27, 2006 10:49 AM
> To: nngau
> Cc: mysql@stripped
> Subject: Re: Multiple primary keys
>
> On 4/27/06, nngau <nngau@stripped> wrote:
>>
>> Can someone figure out what's going on. This is the only change I made
>> to this table. Basically I don't want any duplicate rows, so I setup 4
>> fields to be my primary key.
>>
>> When I do a simple select query it takes nearly 30 seconds to complete.
>> This is affecting my websites and taking a very long time to query the
>> Products. Have I setup this table right? Thank You!!
>>
>> +---------+-------------+------+-----+---------+-------+
>> | Field   | Type        | Null | Key | Default | Extra |
>> +---------+-------------+------+-----+---------+-------+
>> | itemID  | int(6)      |      | PRI | 0       |       |
>> | classA  | int(3)      |      | PRI | 0       |       |
>> | classB  | int(3)      |      | PRI | 0       |       |
>> | classC  | int(3)      |      | PRI | 0       |       |
>> | picture | varchar(10) | YES  |     | NULL    |       |
>> | sex     | char(2)     | YES  |     | NULL    |       |
>> +---------+-------------+------+-----+---------+-------+
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>
> You can avoid duplicate rows with only one primary key, unless you have a
> specific reason for having your primary key span on four columns.
> Your query being slower depends on how you have indexed your columns
> relating to your queries. Please give us a sample query which is running
> slowly..
>
> Kishore Jalleda
> http://kjalleda.googlepages.com/projects
>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
> 


Thread
Update not working in a script, but going fine running with MySQL Browserluis perez27 Apr
  • Re: Update not working in a script, but going fine running withMySQL Browsermysql27 Apr
    • Multiple primary keysnngau27 Apr
      • Re: Multiple primary keyschriswhite27 Apr
      • Re: Multiple primary keysKishore Jalleda27 Apr
        • RE: Multiple primary keysnngau27 Apr
          • Re: Multiple primary keysGordon27 Apr