maria-discuss team mailing list archive

[Alexandre Almeida <alexandre@xxxxxxxxxx>]

Paul,

	Do you any clue why I get this during select?

|  
69305170 
                                                                                             | 
        1 |
|  
69306030 
                                                                                             | 
        2 |
|  
69306290 
                                                                                             | 
        1 |
|  
69306620 
                                                                                             | 
        1 |
|  
69307020 
                                                                                             | 
        1 |
|  
69307040 
                                                                                             | 
        1 |
|  
69307170 
                                                                                             | 
        1 |
|  
ITAPETININGA 
                                                                                         | 
        1 |
|  
R 
                                                                                                    | 
        1 |
| RAUL MOREIRA  
OLIVEIRA 
                                                                                | 
        1 |
| VL  
DALILA 
                                                                                            | 
        1 |
| ¥j§„«m¦‚£l¢€—m‰|ˆhˆ|~_žƒ²[—†Œd“‰ƒjtŒ 
¤më‡úrøøtö{õlò{òdõzómåwärÏq¨x‘sœ‚³xµ€³y¿v
fis->
fis->
fis->

	The statement was: select cep, count(0) from telpbxt group by cep;

	Please, be noticed the query are showing wrong data. The first column  
is ZIP Code (only numbers) and it's showing city name, customer name  
and bourough... These columns are not referenced in this simple query.

	Another weird things is: the query just stopped without error nor  
presenting the time, rows, etc.

	Ooops, the table structure is:

CREATE TABLE `telpbxt` (
  `cpf` bigint(20) DEFAULT NULL,
  `nome` tinytext,
  `log` varchar(100) DEFAULT NULL,
  `endereco` varchar(100) DEFAULT NULL,
  `comp1` varchar(100) DEFAULT NULL,
  `comp2` varchar(100) DEFAULT NULL,
  `bairro` varchar(100) DEFAULT NULL,
  `cidade` varchar(100) DEFAULT NULL,
  `cep` varchar(100) DEFAULT NULL,
  `ddd` varchar(100) DEFAULT NULL,
  `telefone` varchar(100) DEFAULT NULL
) ENGINE=PBXT DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=94

	I have 10.3 mio rows. I know I know this varchar(100) sucks... a lot!


	Thanks for you help.

Alex

Em 06/05/2010, às 03:32, Paul McCullagh escreveu:

> Hi Monty,
>
> On May 5, 2010, at 7:02 PM, Michael Widenius wrote:
>
> > > > > > > "Paul" == Paul McCullagh <paul.mccullagh@xxxxxxxxxxxxx> writes:
> >
> > <cut>
> >
> > Paul> PBXT can help you out with this. Do the following:
> >
> > Paul> (1) Load the telpbxt table with a few 1000 rows (or use  
> > telpbxt2,
> > Paul> since telpbxt already has too many rows in it).
> > Paul> (2) In mysql client enter: CHECK TABLE telpbxt2; (note: do  
> > not do this
> > Paul> on a table with millions of rows. It will take very long to  
> > complete).
> > Paul> (3) Check the MySQL error log, you should see something like  
> > the
> > Paul> output below.
> >
> > Paul> CHECK TABLE: ./test/telpbxt
> > Paul> Record buffer size      = 1024
> > Paul> Fixed length rec. len.  = 1020
> > Paul> Handle data record size = 638
> > Paul> Min/max header size     = 14/26
> > Paul> Min/avg/max record size = 19/624/1019
> > Paul> Avg row len set for tab = not specified
> > Paul> Rows fixed length       = NO
> > Paul> Maximum fixed size      = 16384
> > Paul> ...
> >
> > Paul> Paste the output in an e-mail, and I will show you what to  
> > use for the
> > Paul> AVG_ROW_LENGTH.
> >
> > Would it be hard for PBXT to as part of the CHECK TABLE add:
> >
> > Optimial create AVG_ROW_LENGTH: ?
>
> Currently CHECK TABLE prints:
>
> Average comp. rec. len. = 200
>
> This is the closest to your suggestion of an "Optimal create  
> AVG_ROW_LENGTH", there is at the moment.
>
> But, this value is not necessarily optimal.
>
> Exactly what is optimal depends on a few additional factors, like  
> whether you want to optimize for size or caching behavior.
>
> What I can do is add more information to help in this decision  
> making process, and output a "Recommended AVG_ROW_LENGTH".
>
> Then I can change OPTIMIZE TABLE to automatically set this value, if  
> no explicit AVG_ROW_LENGTH has been set yet.
>
> Best regards,
>
> Paul
>
> --
> Paul McCullagh
> PrimeBase Technologies
> www.primebase.org
> www.blobstreaming.org
> pbxt.blogspot.com