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.
The right index to add depends on your queries and the existing indexes. Do
you already have any indexes on your table? Adding an index dramatically
speeds up queries that use it, but at the cost of slower inserts (each index
must be updated) and larger file size (indexes take space). Thus, the ideal
is to have the minimum set of indexes required for your typical queries.
Your sample query might be helped by a single-column index on item or type,
depending on how restrictive the conditions "item = 23" and "type = 'news'"
are. The multiple-column index on (item, type) you describe would be best for
this particular query (assuming there are more unique items than types), and
others like it that restrict both item and type in the WHERE clause. It would
also function as a single-column index on item, so if you already have one of
those, it could be droppped when you add the multi-column index.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
