Posted by Ja NE on 01/23/07 13:58

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
--

[Back to original message]