Michael Stassen wrote:
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
i am merging about 3 tables (each about 10-15k rows) into one table, so
that is why i asked about when i should create the index (before or
after) since i will be moving things around.
since i have 3 tables which all are similar it is a pain because i have
to repeat a lot of code in the app... this way i can just fetch the rows
i need by specifying the item and type for the apps. so it's a new table
with no data yet which i will dump into from other tables.
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
