Hello, I have a two tables for song information. One just for names (songid, song_title). The other is a chart for song info, which consists of id numbers referencing seperate tables for genres, locations and stats; like so:
SONG_INFO
songid - PRI KEY
genre1_id
genre2_id
region_id
country_id
state_id
city_id
zip_id
//(STATS are SMALL INT, also tracks monthly_ and total_ amounts for each)
weekly_plays
weekly_score
weekly_downloads
Using dropdowns; the song chart allows you to sort by any STAT and/or LOCATION, with the option to narrow that down per GENRE. If a genre is selected; most queries will check to see if either genre1 or genre2 matches, so a query might look like:
WHERE country=27 AND (genre1=33 OR genre2=33) ORDER BY total_plays DESC
So how would I index this? I have a pri key on songid, and a two-column index on genre1-genre2. (since it will either be "gen1=5" or "(gen1=5 or gen2=5)". The nine STAT items are the target of WHERE equally, as are the 5 LOCATION items; so it's tempting to make each STAT and LOCATION an index. Am I asking for trouble with table size and insertion speed? What do you do when every item is worthy of an index? They are all TINY or SMALL INT, so is it not that big of a deal to index them all?
Thanks for any guidance.
