List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 29 1999 12:02pm
Subject:Re: create index question
View as plain text  
On Di, 1999-06-29 09:38:16 +0800, bben wrote:
> I have a table TTT:
> .....
> member tinyint not null default 0,
> openfile tinyint not null default 0,
> score int unsigned not null default 0,
> ......
> 
> I search this table:
> "select * from TTT where member=1 and openfile=1 order by score desc"
> 
> so,how can I create a index on the table for searching it quickly?

First, think about what indexes exactly you need: a common
multi-column index on both columns and/or seperate indexes on the
single columns?  This depends, on what other querys you're going to
issue ...

There's a good discussion of these topics in the MySQL manual, see
chapters "7.2.10 Multiple-column indexes" and "10.4 How MySQL uses
indexes".

Now, how to create indexes.

- Either, define the index already when creating the table
  (recommended):
  
  CREATE TABLE TTT (
    .....
    <your column definitions>,
    INDEX (member, openfile),
    .....
  );

- Or, add the index later on:

  a) CREATE INDEX someindexname ON TTT (member, openfile);

     See "7.26 CREATE INDEX syntax (Compatibility function)".

  b) ALTER TABLE TTT ADD INDEX (member, openfile);

     See "7.7 ALTER TABLE syntax".

Regards,
  Martin

PS: You have space enough for this funny bird, but not for a name?
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
create index questionbben29 Jul
  • Re: create index questionMartin Ramsch29 Jul