need help with query - perfromence probelm

[bergo56]

hi
i have a table called CAR
in the table i have the columns :
ID,WITH_ID,NAME

some of the records in table has the ID value of other records in the WITH_ID column
(e.g record A has ID =123 , and record B and C has WITH_ID =123 , the ID of B and C is diffrent)
for other records the WITH_ID column is empty.

per a given ID i need to fetch all records that has this ID in WITH_ID column - but i cant mark the WITH_ID column as index.
there for i have perfromnce probelm
can i solve this probelm some how?

 

[DonQuichote]

but i cant mark the WITH_ID column as index

Why not?

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[darrellblackhawk]

Are ID and WITH_ID of different types? If so, they shouldn't be.

It sounds like ID and WITH_ID are used for a parent-child relationship or better stated for use in self-joins.

Is this true?

If so, the typical setup would be for the fields to be integer with indexes set.

i.e.
ID - integer, NOT NULL, unsigned, auto-increment, primary index
WITH_ID integer, NULL or default 0, unsigned, regular index