indexing, what and when?

I have been using mysql db for some time but I haven't used indexes...
server was fast and I didn't realized I would need indexes. So I have
never learned, not why do they exist, but what to index. Now, my db is
large enough for queries to became slow and slower and...

but what to index?
id is unique and doesn't have to be indexed. as least I have found
several such explanations,

but if I have, for example, table for gallery with fields:
id (int)
album (int)
user (int)
pict (varchar)
name (varchar)
text (text)
date (timestamp)
category (int)
hit (int)

and later on, another table for comments with fields:
id (int)
group (set) // I'm using one table for all comments on stuff in my site
element (id)
text (text)
date (timestamp)
user (int)

and I have following queries (simplified) for showing one photo and it;s
comments:

SELECT pict,name,text,date,category FROM gallery WHERE id=$foto
while($,$,$,$,$,$) {
SELECT id,text,date,user FROM comments WHERE group='gall' AND
element=$foto
}

what should be indexed?
probably not all tables. I'm not searching or sorting in/by all fields
(rows? btw, what is proper term?). for example, date fields are used
when I want to show to the visitor what have came after his/her last
visit, but hits are shown only for author's own statistics, not for the
rest of us.
when searching for some term I'm searching through fields like name and
text, but text type fields can not be indexed... or could they?

yes, I'm not a programer, that is just my hobby which happen to became
one of larger photo sites in my country... (I'm very modest person :))

tnx in advance
Janko

--
Ja NE
http://fotozine.org/?omen=janimir
--

Re: indexing, what and when?

Ja NE wrote:

> I have been using mysql db for some time but I haven't used indexes...
> server was fast and I didn't realized I would need indexes. So I have
> never learned, not why do they exist, but what to index. Now, my db is
> large enough for queries to became slow and slower and...
>
> but what to index?
> id is unique and doesn't have to be indexed. as least I have found
> several such explanations,
>
> but if I have, for example, table for gallery with fields:
> id (int)
> album (int)
> user (int)
> pict (varchar)
> name (varchar)
> text (text)
> date (timestamp)
> category (int)
> hit (int)
>
> and later on, another table for comments with fields:
> id (int)
> group (set) // I'm using one table for all comments on stuff in my site
> element (id)
> text (text)
> date (timestamp)
> user (int)
>
> and I have following queries (simplified) for showing one photo and it;s
> comments:
>
> SELECT pict,name,text,date,category FROM gallery WHERE id=$foto
> while($,$,$,$,$,$) {
> SELECT id,text,date,user FROM comments WHERE group='gall' AND
> element=$foto
> }
>
> what should be indexed?
> probably not all tables. I'm not searching or sorting in/by all fields
> (rows? btw, what is proper term?). for example, date fields are used
> when I want to show to the visitor what have came after his/her last
> visit, but hits are shown only for author's own statistics, not for the
> rest of us.
> when searching for some term I'm searching through fields like name and
> text, but text type fields can not be indexed... or could they?
>
> yes, I'm not a programer, that is just my hobby which happen to became
> one of larger photo sites in my country... (I'm very modest person :))
>
> tnx in advance
> Janko
>
> --
> Ja NE
> http://fotozine.org/?omen=janimir
> --
(Note: "it;s" should be spelt "its", no punctuation needed for the
possesive in this case)

Regarding the question on fields and rows:
Field and Column are often used interchangeably.
A row is a set of data consisting of one or more fields/columns.

There is a very good chapter in the MySQL manual explaining about
indexes in general:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

In the case that you mention, for the second query, depending on the
actual data that you typically have in the table, one of the following
indexes might be of use:

CREATE INDEX `i1` ON `comments` (`group`, `element`)
CREATE INDEX `i1` ON `comments` (`element`, `group`)
CREATE INDEX `i1` ON `comments` (`group`)
CREATE INDEX `i1` ON `comments` (`element`)

But without futher information, I cannot advise which one

Re: indexing, what and when?

Post removed (X-No-Archive: yes)

Re: indexing, what and when?

> I've tried indexing the information that I most commonly look up and avoid
> text
> and other lengthy column types. If you do a lot of indexing that might use
> up a
> lot of storage space though, so there's probably a balance between speed
> and the
> disk space you use up.

As well as taking up alot of space indexing too much will actually also slow
down queries.