Database question..

[photon]

Does it make sense for an index to be larger than the database itself? Since an index is used to make queries faster, it seems kind of counter-intuitive that it should take up more space than the table itself.

I've got a table with only 3 columns, each is an 8 byte integer.

id, version, and other_id.

id is the primary key, so I assume that gets indexed.

other_id is the other column that's indexed, and currently all are set to null.

The table is populated with lots of rows, and the size of the index on disk is larger than the size of the table itself.

Is it because all the values are null (meaning a selectivity of zero basically)?

ETA: Not sure about the null thing now, the other table I have is two columns (for a many to many relationship), and the index size was more than double. The primary key was a composite key of the two columns, and each column was also indexed because I have to be able to search both ways.

[sclitheroe]

I think it's best not to worry about things like this - the DB knows best. (spoken as a sysadmin, not a SQL guy)

[photon]

Well I ran out of disk space importing data for a clean load that I had already imported a month ago, on the same machine with the same import data into the same database

So I'm trying to figure out what changed, and the only thing I can think of is a few schema changes that impacted the size of the indexes somehow.

It may also be partially related to how the data is imported, I did a test on one of the indexes by dropping it and recreating it and it was 20% smaller (and that was in a non-ideal situation with zero selectivity).

Or a combination, indexes that didn't exist before plus they're not being imported efficiently.

Problem is it's not a nice empty database, even a brand new load has GB's of data and hundreds of millions of rows.

[Bobblehead]

Sure an index can be larger. A lot of what is going on is DB and optimizer dependant. As long as it makes your query faster, that is all that usually matters.

I'd say drop any indexes you can and recreate them after the import.

[photon]

Yeah for sure.

I'm also restoring the old database onto another box to see what the actual size of that was and can compare tables if I have to if the size ends up being wildly different to try and narrow the difference down.

It's just so unexpected for the import not to work, it was just running the same data minutes earlier. One of those "I'm going completely insane" moments.

[photon]

Well fortunately I'm not insane, as usually it turns out one of may basic assumptions was wrong (that I was dealing with the same amount of data).

The size is right, just the one table was missing 80% of the data I thought it would have.

Man dealing with this amount of data is a PITA.