[RESOLVED] Is it nuts to index every column in a 15 column table if they're all queried equally?

Dolemite50

Hello, I have an all-integer table for song info. It consists of the songid, and TINY INTs with ID's referencing seperate tables for genres and locations. It also has 9 STATS such as weekly, monthly, and total plays, downloads and score.
SONG_INFO (condensed)
songid - PRI KEY
genre1_id
genre2_id
region_id
country_id
weekly_plays
weekly_score

The nine STAT items are the target of WHERE equally, as are the 5 LOCATION items. You can use a simple sort like "show songs from England" or reference all like "find the highest weekly_score in Japan for Rap". So what do you do when they are all worthy of an index? I currently have a pri key on songid, and a two-column index on genre1-genre2. They are all TINY or SMALL INT, so is it not that big of a deal to index each column? Speed is more important than disk space and the charts are used many more times a day than songs are uploaded, so insertion times isn't a big concern either.

One last q, I used to have this broke into seperate tables for locations, stats, and genres, each using songid to relate. Should I have left it that way? If I wind up using every column as an index would there be less overhead if broken into seperate tables? None of the info in this table is redundant and contains all integers, so it seems logical to leave it in one table and avoid several JOINS over several tables often. Is that a good idea?

Thanks for any advice.

trexx

You should read more about normalization to understand which attributes (columns) an entity (table) should have and which it shouldn't. here's a short but good article on that subject:

http://www.samspublishing.com/articles/article.asp?p=27281&seqNum=4

short: don't just put something in a table just because it's convenient to select it from there. but you should also avoid 1:1 relationships when they are'nt really necessary. (i guess your tables SONG and SONG_INFO do have a 1:1 relationship g)

About the indexes: It's always nice to have an index. But in large databases it's a matter of diskspace - you can not have an index for every column you'll probably use in a where clause someday. (e.g. our database has more than a terrabyte of data - in that dimensions you don't just create indexes because the fill a whole hard disk).
But i think your song table is small enough - so you may create a few indexes. Anyway... as long as your database runs fast enough without an index - do not use one =)

Dolemite50

Hi trexx,

Thanks again for the help, I really appreciate it.

I've been studying normalization for the past few weeks and have been through that great article. Thanks to Shrike and Weedpacket's advice, and Googled tutorials, I'm on my way to a redundant-free DB. I just have a few straggling questions before I commit 100% because it's a major overhaul. These are things that aren't addressed specifically that I could find.

When you said to avoid 1:1 relationships that aren't necessary, can you please elaborate a little more? The song_info table is currently all integers which I thought would make for speedier queries, but is seperating the song titles into it's own table necessary? Whether the song_title is put in song table or song_info, it is only listed one time in the database. Is it therefore unnecessary to seperate? My assumption was that the all-number table would be speedy to query and then I could just grab the song_title after the calculations using LEFT JOIN on (songid). I would prefer to have them all in one table if I don't take a hit on performance anywhere. The query will already be joining other tables where redundant info was eliminated. (like genre_id or country_id), so I'm liking the sounds of having the song_title in the same table as the rest.

So this is my million dollar question:
I also have a seperate table for song_details that has about 10 columns of items like song_description and lyrics that are ONLY called when on the song page itself. No item in song_details would be in a WHERE statement and it is just used as storage until somebody visits the song_page where it is extracted. I kept them in a seperate table from song_info under the impression that it would be easier to sort through a table with bulky text fields eliminated. However, if mysql ignores the unmentioned columns, and no info in song_details is redundant; am I better off having it all in one table? including song_titles?

trexx, I really hope you can help me out with just that one final question. I'm extremely anxious to get this underway but it's my first normalization attempt and on a site already live with 10,000 members. I'm just hoping to hear how somebody who's been down that road before would go about it. Thank you very much! 🙂

Dolemite50

Hi,

I'm really sorry that I've made two posts in the same night but they are my final two issues before I can dive in and get coding. I've been through the board over and over again and read every tutorial I could find on Normalization, but am left with the above question and one more quickee.

The song table has a VARCHAR(50) field for song_title. I noticed that the only times a member would use that many chars is when adding additional info to the song title such as "featuring Eddie" or "Fresh downtown remix". I would like to reduce the song_title field to VARCHAR(40), (mainly for design purposes) and add an additional subtitle field VARCHAR(25). My q is; since 90% of the songs wouldn't utilize the subtitle field; should it be in a seperate table so that I'm not storing 10,000 empty VARCHAR(25) fields unnecessarily?

trexx

there's no reason to put numeric and char-columns in separate tables. you won't notice a difference when you query a table with mixed values (ok, maybe there IS some very small difference. but see it this way: it surely takes longer to join two tables than to query just one table).

but honestly, there's only one way you'll find out how fast your database-design really is. create somes tables, fill them with a few million randomly generated records and test your query. everything else is way too theoretically =)

but true is: it's easier for a database to join tables when you have only one numeric attribute that identifies an instance. that's one reason why you should use surrogate keys instead of primary keys with multiple attributes when you join tables.

to the varchar: the good thing on VARCHAR is that it is VARiable. So it won't waste space in your table when you don't use it. It's different from CHAR, that always occupies the space you've reserved for it.

Dolemite50

You're the man trexx!!!!!!

Thanks so much, I'm very grateful.