Re: great problem with questions

[DeRyl]

explain example sql question gives:

table  type  possible_keys  key  key_len  ref  rows  Extra
klientslowo range PRIMARY,klientslowo klientslowo 40 NULL 351 Using where; Using
temporary
klientslowa ref klientslowoid klientslowoid 4 klientslowo.klientslowoid 19 Using
index
klient ref klientid,wojewodztwoid klientid 9 klientslowa.klientid 1 Using where
klientmiejscowosci ref KLIENTMIEJSCOWOSCID,KLIENTID KLIENTID 9 klient.klientid 1
Using where; Distinct
klientmiejscowosc eq_ref PRIMARY,KLIENTMIEJSCOWOSC PRIMARY 4
klientmiejscowosci.KLIENTMIEJSCOWOSCID 1 Using where; Distinct
klientulice ref klientulicaid,klientid klientid 9 klient.klientid 1 Using where;
Distinct
klientbranza ref BRANZAID,KLIENTID KLIENTID 9 klient.klientid 2 Using where;
Distinct
branzaslowa ref branzaslowoid,branzaid branzaid 4 klientbranza.BRANZAID 3
Distinct
branzaslowo eq_ref PRIMARY,branzaslowo PRIMARY 4 branzaslowa.branzaslowoid 1
Using where; Distinct
klientulica eq_ref PRIMARY,klientulica PRIMARY 4 klientulice.klientulicaid 1
Using where; Distinct

and the create table for explain tables:

CREATE TABLE `branzaslowa` (
  `branzaid` int(11) NOT NULL default '0',
  `branzaslowoid` int(11) NOT NULL default '0',
  KEY `branzaslowoid` (`branzaslowoid`),
  KEY `branzaid` (`branzaid`)
) TYPE=MyISAM;
CREATE TABLE `branzaslowo` (
  `branzaslowoid` int(11) NOT NULL auto_increment,
  `branzaslowo` varchar(32) default NULL,
  PRIMARY KEY  (`branzaslowoid`),
  KEY `branzaslowo` (`branzaslowo`)
) TYPE=MyISAM AUTO_INCREMENT=1710 ;
CREATE TABLE `klient` (
  `klientid` bigint(14) default NULL,
  `klientnazwaorder` int(11) default NULL,
  `id_inst_nadrz` bigint(14) default NULL,
  `id_isnt_glown` bigint(14) default NULL,
  `wojewodztwoid` int(11) default NULL,
  `powiatid` int(11) default NULL,
  `gminaid` int(11) default NULL,
  `numerporzadkowy` int(11) default NULL,
  `klientnazwa` varchar(250) NOT NULL default '',
  `klientbranza` longtext,
  `ulicaskrot` varchar(20) default NULL,
  `ulicanazwa` varchar(255) default NULL,
  `posesja` varchar(100) default NULL,
  `miejscowosc` varchar(100) default NULL,
  `kodpocztowy` varchar(22) default NULL,
  `powiat` varchar(100) default NULL,
  `gmina` varchar(100) default NULL,
  `wojewodztwo` varchar(100) default NULL,
  `aparatnumer` longtext,
  `www` varchar(100) default NULL,
  `email` varchar(100) default NULL,
  `logo` varchar(100) default NULL,
  `wizytowka` varchar(100) default NULL,
  `wizytowkas` int(11) default NULL,
  `wizytowkaw` int(11) default NULL,
  `struktura` int(11) default NULL,
  `id_regionu` int(11) default NULL,
  `zrodlo` char(1) default NULL,
  `kwotareklam` double default NULL,
  KEY `klientid` (`klientid`),
  KEY `wojewodztwoid` (`wojewodztwoid`)
) TYPE=MyISAM;
CREATE TABLE `klientbranza` (
  `KLIENTID` bigint(14) default NULL,
  `BRANZAID` int(11) default NULL,
  `CZYPODSTAWOWA` int(11) default NULL,
  KEY `BRANZAID` (`BRANZAID`),
  KEY `KLIENTID` (`KLIENTID`)
) TYPE=MyISAM;
CREATE TABLE `klientmiejscowosc` (
  `KLIENTMIEJSCOWOSCID` int(11) NOT NULL default '0',
  `KLIENTMIEJSCOWOSC` char(64) default NULL,
  PRIMARY KEY  (`KLIENTMIEJSCOWOSCID`),
  KEY `KLIENTMIEJSCOWOSC` (`KLIENTMIEJSCOWOSC`)
) TYPE=MyISAM;
CREATE TABLE `klientmiejscowosci` (
  `KLIENTID` bigint(14) default NULL,
  `KLIENTMIEJSCOWOSCID` int(11) default NULL,
  KEY `KLIENTMIEJSCOWOSCID` (`KLIENTMIEJSCOWOSCID`),
  KEY `KLIENTID` (`KLIENTID`)
) TYPE=MyISAM;
CREATE TABLE `klientslowa` (
  `klientid` bigint(14) NOT NULL default '0',
  `klientslowoid` int(11) NOT NULL default '0',
  KEY `klientslowoid` (`klientslowoid`,`klientid`)
) TYPE=MyISAM;
CREATE TABLE `klientslowo` (
  `klientslowoid` int(11) NOT NULL auto_increment,
  `klientslowo` varchar(40) NOT NULL default '',
  PRIMARY KEY  (`klientslowoid`),
  KEY `klientslowo` (`klientslowo`)
) TYPE=MyISAM AUTO_INCREMENT=228453 ;
CREATE TABLE `klientulica` (
  `klientulicaid` int(11) NOT NULL default '0',
  `klientulica` char(96) default NULL,
  PRIMARY KEY  (`klientulicaid`),
  KEY `klientulica` (`klientulica`)
) TYPE=MyISAM;
CREATE TABLE `klientulice` (
  `klientid` bigint(14) default NULL,
  `klientulicaid` int(11) default NULL,
  KEY `klientulicaid` (`klientulicaid`),
  KEY `klientid` (`klientid`)
) TYPE=MyISAM;

-------
Darek

----- Original Message ----- 
From: "Roger Baklund" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: "DeRyl" <[EMAIL PROTECTED]>
Sent: Thursday, September 23, 2004 1:19 PM
Subject: Re: great problem with questions


* DeRyl
> I have  database with 30 tables [some have over 2000k some other
> over 4000k rec]
>
> when I want to run a sql with a few conditions the answer is
> dramatically slow
> [over 70 seconds!]
> sql-s with one condition usually works well..
>
> how is the corrcet way to optimize the database and sql questions?
> the answer from sql should be under 1 second...

I agree. For a start, show us the output of EXPLAIN select distinct logo,
klient.klientid ...

Then we will probably need the output of SHOW CREATE TABLE for some of the
tables, depending on the output of the EXPLAIN command.

--
Roger


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


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