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
