MySQL: Index larger than Data, why?

Forum Moderators: open

Message Too Old, No Replies

MySQL: Index larger than Data, why?

csdude55

11:34 pm on May 16, 2019 (gmt 0)

I have a table with 4 columns:

postdate (date)
which (var of the char, length is 15)
res (enum, with 2 values)
impressions (int, length is 11)

I changed "postdate" from an int with a length of 8, thinking that a date type might be smaller and faster... I was wrong about the size, though! The table went from 6.4M to 8.1M when I changed it :'-( And the index went from 2.4M to 4.3M, so it almost doubled in size!

But anyway. I have one index on it, with postdate, which, and res set to be Unique. In my script, I insert a "1" to impressions, and if it's a duplicate then I increment impressions by 1.

What I don't understand, though, is that the data is 3.8M, and the index is 4.3M! Why? I only have the one index set up, so why is it 15% larger than the entire data?

And is int, length 8 generally faster / smaller than date?

csdude55

11:34 pm on May 16, 2019 (gmt 0)

(sorry for the "var of the char", if I leave out "of the" then I get a 403 Forbidden error when submitting)

tangor

1:27 am on May 17, 2019 (gmt 0)

Indexing always has an overhead. Varies with data types (what is being indexed),

csdude55

2:03 am on May 17, 2019 (gmt 0)

I guess that I'm confused on the "why". If the index is larger than the data, wouldn't it be faster for the database to skip the index?

On a similar note, in PMA, what's the index "size" field do? I can't find anything about it, Google searches only bring up information about the size of the index, not the "size" field. I thought that it would only read X number of characters of the data to make the index, which would make it smaller... but that doesn't seem to be the case.

For example, the "which" field has a length of 15, but each of the 55 possibilities are unique after the first 2 characters. So I thought that setting the size to 3 would be just as good, but make the index smaller. But it turned out to have no impact at all on the size of the index.

phranque

2:49 am on May 17, 2019 (gmt 0)

(sorry for the "var of the char", if I leave out "of the" then I get a 403 Forbidden error when submitting)

iirc not too long ago brett installed something on the WebmasterWorld server to improve db security and it probably rejects anything that looks like sql.