Welcome to WebmasterWorld Guest from 54.161.118.57

Forum Moderators: open

MySQL: Index larger than Data, why?

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

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1116
votes: 109


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?
11:34 pm on May 16, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1116
votes: 109


(sorry for the "var of the char", if I leave out "of the" then I get a 403 Forbidden error when submitting)
1:27 am on May 17, 2019 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member tangor is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Nov 29, 2005
posts:9913
votes: 972


Indexing always has an overhead. Varies with data types (what is being indexed),
2:03 am on May 17, 2019 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member Top Contributors Of The Month

joined:Mar 15, 2013
posts: 1116
votes: 109


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.
2:49 am on May 17, 2019 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:11715
votes: 211


(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.