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" <[EMAIL PROTECTED]>
To: "'Kishore Jalleda'" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
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:[EMAIL PROTECTED]
Sent: Thursday, April 27, 2006 10:49 AM
To: nngau
Cc: mysql@lists.mysql.com
Subject: Re: Multiple primary keys
On 4/27/06, nngau <[EMAIL PROTECTED]> 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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
