Sebastian <[EMAIL PROTECTED]> wrote on 08/04/2005 01:31:13 PM: > i forgot to add another question, > > is it ok to add index after the tables and data are already built, or is
> it better to create the index before data gets inserted? > > Sebastian wrote: > > > I read the manual on indexes but i am a little confused how i should > > build the type of index i need. > > > > i have this structure: > > > > | id | item | type > > -------------------- > > || 3 || 23 || news > > || 4 || 46 || faqs > > || 5 || 23 || news > > || 6 || 23 || news > > > > query: > > > > SELECT id, item, type ... > > FROM comments > > WHERE item = 23 AND type = 'news' > > > > i want a mulitple column index in this case i assume? > > > > INDEX name (item, type); > > > > is this correct? anything else i should know? > > i didn't pay attention to query speed until the site started to > > explode, so i need to speed up queries i created 2 years ago. > > > > thanx. > > > Yes, that two-column index will most likely speed up that type of query. If there is a 3rd column that frequently appears in the WHERE clauses of your queries along with `item` and `type`, you should probably consider making your index out of all 3 columns (with the 3rd column last) rather than just the two columns you already identified. > is it ok to add index after the tables and data are already built, or is > it better to create the index before data gets inserted? For bulk data loading and other INSERT/UPDATE operations that affect large numbers of rows (especially when building your table the first time), you should be able to setup your data much faster without the indexes in place. Also, since you are creating/modifying the indexes only once (and in bulk) it should take less TOTAL TIME (populate time + indexing time) with later indexing than if you had the indexes in place during the initial load. I am sorry you waited so long to optimize your queries but better now than later. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
