MYSQL INDEX

[volang]

Hello guys!

I have a table with two columns and 150 000 000 rows. Now I wonder, If I create a index for one of these colums, does this mean it will give me 150 000 000 extra rows behind the scenes? (for that specific column that I've created a index on)


//V

[nuderobmonkey]

An index works like a (single) pointer to the values, so it should not increase the data volume. That's true for a consecutive index like std::array, std::vector and others where data is consecutive stored and where the access of the index is also in consecutive manner.

[volang]

Let's say I create a ASC index on one colum, then a single pointer can't represent ALL the values in that column I think.

[helios]

(Just FYI, this question is not on-topic for this forum.)

An index in a relational table does have a considerable size, roughly proportional to the row count of the table, but it's not the same as just having twice as many rows; if that was the case then nobody would use them.
The exact size increase depends on the columns data type, the data distribution (e.g. does every row contain unique values, or do many rows have duplicate values?), etc.

See also: https://en.wikipedia.org/wiki/Database_index

[volang]

(Just FYI, this question is not on-topic for this forum.)

I'll keep this in mind.

Yes. But pointers is also data, and keeping track of the order of a entire column would still mean extra disc space.

So I dont know if this is right:
"so it should not increase the data volume"

My guess is that it should increase more than just a little for a DESC index, 150 000 000 rows.

But how can I keep track of how much space the database is taking up on my machine? (including all tables, indexes and so on) .

I found some good stuff for myisam but i'm using innodb

[helios]

So I dont know if this is right:
"so it should not increase the data volume"

It's not. An index will make the table larger, but how much exactly is difficult to say up-front.

But how can I keep track of how much space the database is taking up on my machine? (including all tables, indexes and so on) .

Any MySQL administration console should be able to give you statistics on space usage per table, including the size of all (or each) indexes.

[volang]

helios, thank you