How to index views for faster queries?

[dualfragment]

My MySQL database is structured so that each user has 9 items. Each item can have multiple numerical entries. What my query does is finds the maximum entry for each of those 9 items, then adds the largest value from each up.

I created 2 views to do portions of this:

CREATE ALGORITHM=MERGE VIEW SumCampaignScores AS
SELECT Sum(MaxCampaignScore) AS SumCampaignScore, Gamertag
FROM MaxCampaignScores GROUP BY Gamertag;

CREATE ALGORITHM=MERGE VIEW MaxCampaignScores AS
SELECT Max(Score) AS MaxCampaignScore, Gamertag, MissionID FROM CampaignMissions
GROUP BY Gamertag, MissionID;

How do I add indices for those?

This is a slow query, for example:
SELECT COUNT(*) FROM SumCampaignScores

Any suggestions?

[carguy84]

I'm not at all familiar with MySQL, but indexes are assigned on a column in a table. You want to create the index on the columns you are selecting frequently.

[phranque]

you could try something like this:

CREATE UNIQUE INDEX SumCampaignScores (Gamertag)

if you do this it may help you figure out which column(s) to index:

EXPLAIN SELECT COUNT(*) FROM SumCampaignScores