Posted by Tom on 01/23/07 18:17

On Tue, 23 Jan 2007 14:58:51 +0100, 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
>



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.



Tom
--
Newsguy.com
75+ days of Binary and Text Retention!
Higher levels of article completion!
Broader coverage of newsgroups

[Back to original message]