Two independent Indexes - Question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Calimero
Forum Contributor
Posts: 310
Joined: Thu Jan 22, 2004 6:54 pm
Location: Milky Way

Two independent Indexes - Question

Post 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 ! :)
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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?
lostboy
Forum Contributor
Posts: 329
Joined: Mon Dec 30, 2002 8:12 pm
Location: toronto,canada

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

lostboy wrote:...consider a full text index on that
fulltext indexes aren't used when performing LIKE comparision.
Post Reply