|
|
Posted by Captain Paralytic on 01/23/07 14:26
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
[Back to original message]
|