How to calculate Table Index Size estimate ?

Hi

I want to calculate Table Index estimate.
Please teach me how to do that!!

I think that it below$B"-(Bexactly?

column size for Index * Table records size = Table index Size estimate

best regards!

"$BN4B@0KF#(B" <ipdragon181818@gmail.com> wrote in message
news:b47e9047-af58-4b72-bfec-61e9810257ff@googlegroups.com...
> Hi
>
> I want to calculate Table Index estimate.
> Please teach me how to do that!!
>
> I think that it below$B"-(Bexactly?
>
> column size for Index * Table records size = Table index Size estimate
>
> best regards!

Others will correct me if I'm wrong, but the best you will be able to do is
to provide an ESTIMATE, you won't be able to get it exactly. The reason is
that the space taken up by an index will change as records are added and
deleted even if the total number of records remains the same.

I would expect an estimate to be closer to:
(field size for index fields + field size for primary key fields + other
over head) x number of records

A better approach is probably to play with some sample data and extract real
figures from that and use that as a basis for your estimate. The following
query will list all the tables in your database together with the data size
and index size:

SELECT table_name,engine,
ROUND(data_length/1024/1024,2) total_size_mb,
ROUND(index_length/1024/1024,2) total_index_size_mb, table_rows
FROM information_schema.TABLES
WHERE table_schema = Database()

Hope this helps.
--
Brian Cryer
http://www.cryer.co.uk/brian

On Jun 22, 10:10 am, "Brian Cryer" <not.h...@localhost.invalid> wrote:
> "$BN4B@0KF#(B" <ipdragon181...@gmail.com> wrote in message
>
> news:b47e9047-af58-4b72-bfec-61e9810257ff@googlegroups.com...
>
> > Hi
>
> > I want to calculate Table Index estimate.
> > Please teach me how to do that!!
>
> > I think that it below$B"-(Bexactly?
>
> > column size for Index * Table records size = Table index Size estimate
>
> > best regards!
>
> Others will correct me if I'm wrong, but the best you will be able to do is
> to provide an ESTIMATE, you won't be able to get it exactly.

So the OP says he wants to calculate a "Table index Size estimate" and
you warn him that " the best you will be able to do is to provide an
ESTIMATE".

Just which part of him saying that all he expected to get was an
estimate wasn't clear?

"Captain Paralytic" <paul_lautman@yahoo.com> wrote in message
news:4f1d6117-4bfb-4ab0-a5dd-8857bb13325b@b1g2000vbb.googlegroups.com...
> On Jun 22, 10:10 am, "Brian Cryer" <not.h...@localhost.invalid> wrote:
>> "$BN4B@0KF#(B" <ipdragon181...@gmail.com> wrote in message
>>
>> news:b47e9047-af58-4b72-bfec-61e9810257ff@googlegroups.com...
>>
>> > Hi
>>
>> > I want to calculate Table Index estimate.
>> > Please teach me how to do that!!
>>
>> > I think that it below$B"-(Bexactly?
>>
>> > column size for Index * Table records size = Table index Size estimate
>>
>> > best regards!
>>
>> Others will correct me if I'm wrong, but the best you will be able to do
>> is
>> to provide an ESTIMATE, you won't be able to get it exactly.
>
> So the OP says he wants to calculate a "Table index Size estimate" and
> you warn him that " the best you will be able to do is to provide an
> ESTIMATE".
>
> Just which part of him saying that all he expected to get was an
> estimate wasn't clear?

Since the OP used the word "exactly" I was making it clear that the best you
can get is an estimate. Since the script used in the OP's name would
indicate that English might not be his/her first language, I thought it
worth emphasising the point.

BTW: Your email doesn't look like its munged, is that intentional?
--
Brian Cryer
http://www.cryer.co.uk/brian

On Jun 28, 5:16 pm, "Brian Cryer" <not.h...@localhost.invalid> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
>
> news:4f1d6117-4bfb-4ab0-a5dd-8857bb13325b@b1g2000vbb.googlegroups.com...
>
>
>
>
>
>
>
>
>
> > On Jun 22, 10:10 am, "Brian Cryer" <not.h...@localhost.invalid> wrote:
> >> "$BN4B@0KF#(B" <ipdragon181...@gmail.com> wrote in message
>
> >>news:b47e9047-af58-4b72-bfec-61e9810257ff@googlegroups.com...
>
> >> > Hi
>
> >> > I want to calculate Table Index estimate.
> >> > Please teach me how to do that!!
>
> >> > I think that it below$B"-(Bexactly?
>
> >> > column size for Index * Table records size = Table index Size estimate
>
> >> > best regards!
>
> >> Others will correct me if I'm wrong, but the best you will be able to do
> >> is
> >> to provide an ESTIMATE, you won't be able to get it exactly.
>
> > So the OP says he wants to calculate a "Table index Size estimate" and
> > you warn him that " the best you will be able to do is to provide an
> > ESTIMATE".
>
> > Just which part of him saying that all he expected to get was an
> > estimate wasn't clear?
>
> Since the OP used the word "exactly" I was making it clear that the best you
> can get is an estimate.
He meant is this exactly the right way to do it.

On Jun 28, 5:16*pm, "Brian Cryer" <not.h...@localhost.invalid> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
> BTW: Your email doesn't look like its munged, is that intentional?

Yep it is intentional. I don't often use my yahoo mail so I don't tend
to mind how much spam it gets.