RE: Performance Tuning - Table Joins

[j llarens]

I wonder how somebody can approve such a query and tables.

1) There is no indexes, thats the main reason for the time and cpu consuming. 
At least,
the fields used in the joins MUST be indexed.

2) varchar(255) on ALL fields? That's unlikely, in-cre-di-ble. Right type for 
the right
data, int for numbers, float for money, char for fixed string, and certainly 
NOT 255 for
lenght! If its necesary such amount of characters, TEXT or BLOB must be used, 
but only if
it is necesary.

CREATE TABLE `memupdate` (
   `MemID` int(10) NOT NULL default 0,
   `Status` varchar(255) NOT NULL default '',
   `Salut` varchar(255) NOT NULL default '',
   `First` varchar(255) NOT NULL default '',
   `Middle` varchar(255) NOT NULL default '',
   `Last` varchar(255) NOT NULL default '',
   `Suffix` varchar(255) NOT NULL default '',
   `SSN` varchar(255) NOT NULL default '',
   `Sex` varchar(255) NOT NULL default '',
   `Admit` varchar(255) NOT NULL default '',
   `Birth` varchar(255) NOT NULL default '',
   `Exam` varchar(255) NOT NULL default '',
   `School` varchar(255) NOT NULL default '',
   `Company` varchar(255) NOT NULL default '',
   `Add1` varchar(255) NOT NULL default '',
   `Add2` varchar(255) NOT NULL default '',
   `City` varchar(255) NOT NULL default '',
   `State` varchar(255) NOT NULL default '',
   `Zip` varchar(255) NOT NULL default '',
   `Country` varchar(255) NOT NULL default '',
   `Phone` varchar(255) NOT NULL default '',
   `Fax` varchar(255) NOT NULL default '',
   `Circuit` varchar(255) NOT NULL default '',
   `County` varchar(255) NOT NULL default '',
   `Year` varchar(255) NOT NULL default '',
   `Email` varchar(255) NOT NULL default '',
   `LastUpdated` varchar(255) NOT NULL default '',
primary key (memID)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `tblcc` (
   `record_status` varchar(255) NOT NULL default '',
   `grp0` varchar(255) NOT NULL default '',
   `member` int(10) NOT NULL default 0,
   `appl_year` varchar(255) NOT NULL default '',
   `course_grp0` varchar(255) NOT NULL default '',
   `sponsor` varchar(255) NOT NULL default '',
   `course` varchar(255) NOT NULL default '',
   `dater` varchar(255) NOT NULL default '',
   `sub` varchar(255) NOT NULL default '',
   `date_maint` varchar(255) NOT NULL default '',
   `who_maint` varchar(255) NOT NULL default '',
   `seq_maint` varchar(255) NOT NULL default '',
   `sp_require` varchar(255) NOT NULL default '',
   `appl_hrs_01` varchar(255) NOT NULL default '',
   `appl_hrs_02` varchar(255) NOT NULL default '',
   `appl_hrs_03` varchar(255) NOT NULL default '',
   `appl_hrs_04` varchar(255) NOT NULL default '',
   `appl_hrs_05` varchar(255) NOT NULL default '',
   `appl_hrs_06` varchar(255) NOT NULL default '',
   `type` varchar(255) NOT NULL default '',
   `fee` varchar(255) NOT NULL default '',
   `comment_flag` varchar(255) NOT NULL default '',
   `paid` varchar(255) NOT NULL default '',
   `fee_override` varchar(255) NOT NULL default '',
   `charge` varchar(255) NOT NULL default '',
   `attend_type` varchar(255) NOT NULL default '',
   `instructor` varchar(255) NOT NULL default '',
   `date_taken` date NOT NULL default '',
index memindex (member),
index a_year (appl_year),
index d_taken (date_taken)
ENGINE=InnoDB DEFAULT CHARSET=latin1

Im assuming memupdate has unique values for memID, thus, the primary key over 
memID
- primary key (memID)
- index memindex (member)

index for the where
-index a_year (appl_year)

index fot order
-index d_taken (date_taken)

For a performance issues, is better that fields in a join be of the same type 
and lenght
-   `MemID` int(10) NOT NULL default 0,
-   `member` int(10) NOT NULL default 0,

data types
-`appl_year` int(4) NOT NULL default 0,
-`date_taken` date NOT NULL default '',

Who made that tables and query really REALLY needs to RTFM :)

http://dev.mysql.com/doc/mysql/en/select-speed.html






___ 
250MB gratis, Antivirus y Antispam 
Correo Yahoo!, el mejor correo web del mundo 
http://correo.yahoo.com.ar

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

update problem with mysqlimport (bug/misuse?)

[j llarens]

Hi people
I'm facing a (not huge) problem with mysqlimport.

The mysql version I'm using is MySQL
4.0.11a-gamma'-Max'

For updating a #29000 records table from fixed-lenght
ASCII file, I'm using a php script that gets a record
and executes and UPDATE for each one: pretty SLOW.

SO I read carefully mysqlimport and think that is THE
solution for the speed matter. BUT the fields that I
don't include in the mysqlimport field list get EMPTY!


Here are the details:

This is the table:

CREATE TABLE alfabeta (
  id mediumint(5) unsigned NOT NULL default '0',
  nombre varchar(30) NOT NULL default '',
  presenta varchar(30) NOT NULL default '',
  precio float(7,2) unsigned NOT NULL default '0.00',
  precio_ponderado float(7,2) unsigned NOT NULL
default '0.00',
  fecha_mod date NOT NULL default '-00-00',
  id_psico tinyint(1) NOT NULL default '0',
  anexo tinyint(1) unsigned NOT NULL default '0',
  pami char(1) NOT NULL default '0',
  id_laboratorio smallint(4) unsigned NOT NULL default
'0',
  baja tinyint(1) unsigned NOT NULL default '0',
  heladera tinyint(1) unsigned NOT NULL default '0',
  id_troquel int(10) unsigned NOT NULL default '0',
  id_monodroga mediumint(6) unsigned NOT NULL default
'0',
  id_acfa smallint(5) unsigned NOT NULL default '0',
  id_codbarra bigint(13) unsigned NOT NULL default
'0',
  unidades smallint(4) unsigned NOT NULL default '0',
  importado tinyint(1) unsigned NOT NULL default '0',
  sifar char(1) NOT NULL default ,
  id_tamano smallint(2) unsigned NOT NULL default '0',
  id_tipounid smallint(4) unsigned NOT NULL default
'0',
  id_tipovta tinyint(1) NOT NULL default '0',
  id_acciofar int(2) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY id_troquel (id_troquel),
  KEY nombre (nombre,presenta)
) TYPE=MyISAM;

The mysqlimport command:

mysqlimport osdata -u owner -pofthedata --debug -c
id,nombre,presenta,precio,id_psico,pami,id_laboratorio,baja,heladera,id_troquel,id_codbarra,unidades,importado,sifar,id_tamano,id_tipovta
-r -v -l alfabeta.csv

Note: the original file alfabeta.txt have fixed-lenght
fields, I use an awk script to convert to alfabeta.csv

The question is that, the field precio_ponderado, for
example, comes from another source, NOT from
alfabeta.csv, so I load it from another .csv in
another .php script. But after the execution of the
mysqlimport, it gets 0.00, when, well, I expect that
mysqlimport leaves it unchanged because I don't list
it in the -c clause.


So, is that a bug or mysqlimport is only useful for
populating empty tables and for that reason emptyes
the values of the columns not listed?

If so, I'll must to discard that sooo elegant solution
and use a .php script to make a HUGE .sql file with
UPDATE's (IdontwantitIdontwantitIdontwantit!)

thanks in advance
Jorge Llarens





___
100mb gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]