MySQL Query tuning

[mrtblt]

Merhaba Arkadaslar,

PHP ile DB den veri cekerek musteri listesi olusturuyorum. Bu listede musterilerin kalan siparis miktarlari, alacak, borc ve bakiye durumlari da gorunuyor. Ben bu listeyi olustururken cok ilkel bir sorgulama yolu kullnaiyorum. Tablolar asagidaki gibidir :

Musteri

Kod: Tümünü seç

CREATE TABLE IF NOT EXISTS `clients` (
  `id` int(11) NOT NULL auto_increment,
  `user_name` varchar(20) NOT NULL,
  `pass` varchar(20) NOT NULL,
  `cl_code` varchar(10) NOT NULL,
  `cl_name` varchar(40) NOT NULL,
  `shop_name` varchar(30) NOT NULL,
  `contact_person` varchar(40) NOT NULL,
  `address_inv` varchar(80) NOT NULL,
  `address_dlv` varchar(80) NOT NULL,
  `city` varchar(40) NOT NULL,
  `judet` varchar(40) NOT NULL,
  `tel_fix` varchar(15) NOT NULL,
  `mobil1` varchar(15) NOT NULL,
  `mobil2` varchar(15) NOT NULL,
  `email` varchar(30) NOT NULL,
  `cui` varchar(20) NOT NULL,
  `cif` varchar(15) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `iban` varchar(30) NOT NULL,
  `approved` char(1) NOT NULL,
  `suspended` char(1) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `cl_code` (`cl_code`,`cl_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=79 ;

Cek-senet

Kod: Tümünü seç

CREATE TABLE IF NOT EXISTS `ceksenet` (
  `id` int(11) NOT NULL auto_increment,
  `tip` char(1) NOT NULL,
  `cl_id` varchar(3) NOT NULL,
  `miktar` decimal(7,2) NOT NULL,
  `vade` date NOT NULL,
  `kayit` date NOT NULL,
  `seri` varchar(20) NOT NULL,
  `odeme` varchar(20) NOT NULL,
  `banka` varchar(20) NOT NULL,
  `status` char(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

Faturalar

Kod: Tümünü seç

CREATE TABLE IF NOT EXISTS `inv_header` (
  `id` int(11) NOT NULL auto_increment,
  `inv_no` varchar(10) collate utf8_romanian_ci NOT NULL,
  `client_id` varchar(11) collate utf8_romanian_ci NOT NULL,
  `inv_date` date NOT NULL,
  `inv_type` varchar(15) collate utf8_romanian_ci NOT NULL,
  `adet` int(11) NOT NULL,
  `net_sum` decimal(7,2) NOT NULL,
  `vat_sum` decimal(7,2) NOT NULL,
  `disc_prcnt` int(2) NOT NULL,
  `disc_val` decimal(7,2) NOT NULL,
  `grand_sum` decimal(7,2) NOT NULL,
  `inv_exp` varchar(2) collate utf8_romanian_ci NOT NULL,
  `exch_rate` decimal(5,2) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci AUTO_INCREMENT=33 ;

Cari Fisleri

Kod: Tümünü seç

 CREATE TABLE `rabitto_shop`.`cfis` (
`id` INT NOT NULL AUTO_INCREMENT ,
`tip` CHAR( 1 ) NOT NULL ,
`cl_id` VARCHAR( 3 ) NOT NULL ,
`tarih` DATE NOT NULL ,
`miktar` DECIMAL( 7, 2 ) NOT NULL ,
`aciklama` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM 

Siparis baslik

Kod: Tümünü seç

CREATE TABLE IF NOT EXISTS `sip_header` (
  `id` int(11) NOT NULL auto_increment,
  `sip_no` varchar(10) collate utf8_romanian_ci NOT NULL,
  `client_id` varchar(11) collate utf8_romanian_ci NOT NULL,
  `sip_date` date NOT NULL,
  `grand_set` int(11) NOT NULL,
  `grand_adet` int(11) NOT NULL,
  `net_sum` decimal(7,2) NOT NULL,
  `vat_sum` decimal(7,2) NOT NULL,
  `grand_sum` decimal(7,2) NOT NULL,
  `sip_exp` varchar(80) collate utf8_romanian_ci NOT NULL,
  `exch_rate` decimal(5,2) NOT NULL,
  `status` char(1) collate utf8_romanian_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci AUTO_INCREMENT=47 ;

Siparis alt tablo

Kod: Tümünü seç

CREATE TABLE IF NOT EXISTS `sip_lines` (
  `id` int(11) NOT NULL auto_increment,
  `sipno_id` varchar(10) collate utf8_romanian_ci NOT NULL,
  `code_id` varchar(10) collate utf8_romanian_ci NOT NULL,
  `color` varchar(25) collate utf8_romanian_ci NOT NULL,
  `amount` int(11) NOT NULL,
  `set_amount` int(11) NOT NULL,
  `unit_price` decimal(7,2) NOT NULL,
  `line_net` decimal(7,2) NOT NULL,
  `line_vat` decimal(7,2) NOT NULL,
  `line_grand` decimal(7,2) NOT NULL,
  `status` char(1) collate utf8_romanian_ci NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `sip_no` (`sipno_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_romanian_ci AUTO_INCREMENT=1223 ;

Ben bu listeyi ekrana yazdirirken asagidaki gibi bir sorgulama sekli kullaniyorum

Kod: Tümünü seç

		$query=mysql_query("select * from clients order by judet ASC");
		while($row = mysql_fetch_object($query))
		{

		$q1=mysql_query("select sum(miktar) as cs_g from ceksenet where cl_id='$row->id'");
		$r1 = mysql_fetch_object($q1);
		$q2=mysql_query("select sum(grand_sum*exch_rate*(100-disc_prcnt)/100) as ft_c from inv_header where client_id='$row->id'");
		$r2 = mysql_fetch_object($q2);
		$q3=mysql_query("select sum(amount*set_amount) as sip from sip_lines where status='0' and sipno_id in (select id from sip_header where client_id='$row->id') ");
		$r3 = mysql_fetch_object($q3);

........

Gordugunuz gibi musteri id yi referans alip sorgu icerisinde sorgu yapiyorum. Nasil bu isi tek sorguda bitirebilecegimi bilmemekle beraber bu tarzin cok ilkel olduguna eminim

Asagidaki sartlari saglayarak yukaridaki yapiyi nasil tek sorgulu bir yapiya donusturebilirim

1 - siparis baslik tablosundan ilgili client_id ye ait siparis id leri bulunacak ve bu siparis id ler'ine karsilik gelen sip_lines tablosundaki (sip_lines te sipno_id mevcut. Bu deger sip_header deki id ye takabul eder) siparislerden status=0 degerini saglayan degerlerin toplami bulunacak

2 - Borc bulunacak

2 a) ceksenet tablosundan cl_id degeri musteri tablosunun id degeri ile esit olan kayitlardan status=1 ve status=2 degerlerinin toplami bulunacak

2 b) inv_header tablosundan client_id degeri musteri tablosunun id degeri ile esit olan kayitlardan inv_type=1 ve inv_type=2 degerlerinin toplami

2 c) cfis tablosundan cl_id degeri musteri tablosunun id degeri ile esit olan kayitlardan tip=1 ve tip=2 degerlerinin toplami



3 - Alacak bulunacak

3 a) ceksenet tablosundan cl_id degeri musteri tablosunun id degeri ile esit olan kayitlardan status=3 ve status=4 degerlerinin toplami bulunacak

3 b) inv_header tablosundan client_id degeri musteri tablosunun id degeri ile esit olan kayitlardan inv_type=3 ve inv_type=4 degerlerinin toplami

3 c) cfis tablosundan cl_id degeri musteri tablosunun id degeri ile esit olan kayitlardan tip=3 ve tip=4 degerlerinin toplami

bunlar bulundugunda bakiyeyi bulmak zaten kolay.

Burada ilk baktiginizda hazirdan birileri benim icin kod yazsin gibi bir yaklasim icerisinde oldugumu dusunebilirsiniz. Fakat amacim bunu gercekten nasil yapabilecegimi ogrenmek. Cunku eski sistemde devam etsem, ki bu sekilde bircok sorgu var, hem muhtemelen cok performans kaybi olacak hemde cok karmasik bir kodyapisi ortaya cikacak. Ama bu yapiyi yani 3-4 tabloyu ayni anda kullanarak bir sorgu olusturabilmek sanirim bana cok mesafe aldiracak.

Bu hususta yardimci olabilecek arkadaslara simdiden tesekkur ederim

[mrtblt]

Yaptigim arastirmalara gore burada join kullanmak gerekiyor ama bahsettigim kondisyonlara gore toplama vs islemler kafami karistiriyor biraz. Sanki burada elmayla armutu toplamam gerekiyor gibi geliyor

[mkysoft]

Gerçekten büyük bir proje. Bir sürü tablo var. Bunları kendis sitemime create edip içine test verisi girmek çok zahmetli geldi. Hazır verili bir sistemi kullanabileceğimiz bir yapı oluşturursanız daha kolay yardımcı olabiliriz.

[mrtblt]

Istedigin veriler burada hocam

http://www.kumyapi.com.tr/deneme/rabitto_shop.sql

[onaydin]

Tablo yapısına vs. bakmadan sadece çekilen selectleri görüp şöyle birşey olabileceğini tahmin ediyorum

select * from clients order by judet ASC, anladığım burdan id yi alıyoruz, diğer sql lerde tek satır sonuç dönüyor o zaman

Kod: Tümünü seç

select c.*, 
(select sum(miktar) as cs_g from ceksenet where cl_id=c.id) as birinci_sql,  
(select sum(grand_sum*exch_rate*(100-disc_prcnt)/100) as ft_c from inv_header where client_id=c.id) as ikinci_sql,
(select sum(amount*set_amount) as sip from sip_lines where status='0' and sipno_id in (select id from sip_header where client_id=c.id)) as ucuncu_sql
from clients c order by judet ASC

clients e c diye bir alias verdik, sonrada row-id yi çekmek yerine onun id sini getirdik.

Mysql 4.1+ da çalışır, denemek lazım.