Page 1 of 1

Two independent Indexes - Question

Posted: Wed Dec 15, 2004 4:37 am
by Calimero
First off all - greetings to those who are reading this post :)


Ok.

This is the problem:


Structure of the tables:

Table1: ID, Description

Table2: ID, Table1_id

Table3: ID, Other_columns


What I want to do = QUERY:\

Code: Select all

SELECT t3.other_columns 

FROM table1 t1, table2 t2, table3 t3

WHERE t2.table1_id = t1.id AND t3.id = t2.id AND t1.description like '%some_variable%'

Seems straight forward till now - aint it :D

I want to use 2 indexes one on t2.id and other on t2.table1_id - and they are not connected in any way - I used two create index statements.


So will I achieve any speed increase if I use index on t2.id or not - none of the two columns from table2 will be displayed - they are just used for comparing.


AND Yes I know this could be done with only two tables, the idea is far more complex, so I need this intemediate table - the question is just about indexes and speed.


Thanks Ahead ! :)

Posted: Wed Dec 15, 2004 4:43 am
by Weirdan
what does

Code: Select all

EXPLAIN SELECT t3.other_columns

FROM table1 t1, table2 t2, table3 t3

WHERE t2.table1_id = t1.id AND t3.id = t2.id AND t1.description like '%some_variable%'
show?

Posted: Wed Dec 15, 2004 10:55 am
by lostboy
indeces will speed up the select query, note that primary keys have an index on them by default. The slowest part will likely be matching the text description...consider a full text index on that

Posted: Thu Dec 16, 2004 4:30 am
by Weirdan
lostboy wrote:...consider a full text index on that
fulltext indexes aren't used when performing LIKE comparision.