composite keys indexing

2003-04-01 Thread Jinesh Varia 
Hello group,

I have a simple composite key
create table a_b (aID int NOT NULL, bID int NOT NULL, primary key
(aID,bID));

Where aID is primary key in the table a and bID is the primary key in table b,

While my primary keys are automatically indexed, I am having problems when I use the 
bID.

I read the documentation about only the first column will be actually indexed, but is 
there any
workaround (I am using 4.0 mysql version)

for ex,
when I use
select a.* from a where a_b.aID=34;

the query is damn fast!! it is only indexing the aID.

but when I use

select a.* from a,a_b where a_b.bID=55 and a_b.aID=a.aID;

this query is taking hours... I have a huge database.

Is there any work around?

Helps appreciated!

Jin


=
-
Jinesh Varia
Graduate Student, Information Systems
Pennsylvania State University
Email: [EMAIL PROTECTED]
-
'Self is the author of its actions.'

__
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: composite keys indexing

2003-04-01 Thread Michael Brunson 
On Tue, 1 Apr 2003 20:30:32 -0800 (PST), Jinesh Varia used a few
recycled electrons to form:
| Hello group,
| 
| I have a simple composite key
| create table a_b (aID int NOT NULL, bID int NOT NULL, primary key
| (aID,bID));
| 
| Where aID is primary key in the table a and bID is the primary key in table b,
| 
| While my primary keys are automatically indexed, I am having problems when I use the 
bID.
| 
| I read the documentation about only the first column will be actually indexed, but 
is there any
| workaround (I am using 4.0 mysql version)
| 
| for ex,
| when I use
| select a.* from a where a_b.aID=34;
| 
| the query is damn fast!! it is only indexing the aID.
| 
| but when I use
| 
| select a.* from a,a_b where a_b.bID=55 and a_b.aID=a.aID;

Try:

select a.* from a_b left join a using aID where a_b.bID=55;

This will be a lot faster, since you are restricting your result
set, before doing the join.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]