MySQL Performance: MySQL => Index Question

Members

Help

Home

Home » Performance » MySQL » Index Question

Show: Today's Messages :: Show Polls :: Message Navigator

Index Question [message #1237]

Fri, 11 May 2007 14:24

esudnik
Messages: 9
Registered: March 2007

Junior Member

I have following index question:

First a have this table definition:

mytable
- id (pk)
- column1 (fk to any other table)
- column2
index 1: id
index 2: column1

...when a call this query, then it is not very fast because only one column index is used
select * from mytable where column1 = ?? order by column2

...then a changed mytable to this:
mytable
- id (pk)
- column1 (fk)
- column2
index 1: id
index 2: column1, column2

in this table I created index with both columns and droped column1 index

and now my question: is there any other performance problem when a do this?

Report message to a moderator

Re: Index Question [message #1290 is a reply to message #1237 ]

Tue, 22 May 2007 15:52

shobana
Messages: 1
Registered: May 2007

Junior Member

Since you have only one where clause. It okay to index just one column(primary key).

Sort by happens after the query retrieval and this doesn't depend on the index on foreign key column2.

Hence it wise to just have one index, unless you join with another table.

Regards
Shobana

Report message to a moderator

Previous Topic:	Very slow connection time over network.
Next Topic:	which is fastest: a join or one big table?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 9 21:10:16 EDT 2009

Total time taken to generate the page: 0.00956 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.