Forum PHP Romania - Discutii despre PHP, MySQL, Javascript, AJAX, etc

[asavin]

Salutare!

Am mai multe tabele dupa cum urmeaza :

Cod: CREATE TABLE IF NOT EXISTS `artlist_codes` (
  `artlist_codes_id` mediumint(9) NOT NULL auto_increment,
  `artcode` varchar(7) NOT NULL,
  `subsupp_article` varchar(255) default NULL,
  `maker_article` varchar(255) default NULL,
  `rev_article` varchar(255) default NULL,
  `desc_en` text,
  `desc_ro` text,
  `unit_en` varchar(255) default NULL,
  `unit_ro` varchar(255) default NULL,
  `price` double default '0',
  `supplier_id` smallint(6) default NULL,
  `stock_min` double default NULL,
  `stock_max` double default NULL,
  `stock_group` varchar(255) default NULL,
  `old_artcode` varchar(7) default NULL,
  `status` enum('1','0') default '1',
  `comp_id` tinyint(4) NOT NULL COMMENT 'ID Companie',
  `date_sys` datetime NOT NULL,
  `username` varchar(255) NOT NULL,
  PRIMARY KEY  (`artlist_codes_id`),
  KEY `artcode` (`artcode`),
  KEY `supplier_id` (`supplier_id`),
  KEY `comp_id` (`comp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5571 ;

CREATE TABLE IF NOT EXISTS `artlist_supp` (
  `artlist_supp_id` smallint(6) NOT NULL auto_increment,
  `artlist_codes_id` mediumint(9) NOT NULL,
  `contacts_id` smallint(6) NOT NULL,
  `buffer_time` tinyint(4) default '0',
  `delivery_time` tinyint(4) default '0',
  `pay_terms` tinyint(4) default '0',
  `history_rating` enum('-1','1','2','3','4','5','6','7','8','9','10') NOT NULL default '-1' COMMENT '''-1''=no rating, ''x''=rating',
  `quality_rating` enum('-1','1','2','3','4','5','6','7','8','9','10') NOT NULL default '-1' COMMENT '''-1''=no rating, ''x''=rating',
  `price` float default '0',
  `transport` varchar(100) default NULL,
  `comments` text,
  `status` enum('-1','1') NOT NULL default '1' COMMENT '''-1''=deleted,''1''=enabled',
  PRIMARY KEY  (`artlist_supp_id`),
  KEY `artlist_codes_id` (`artlist_codes_id`),
  KEY `contacts_id` (`contacts_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9046 ;

CREATE TABLE IF NOT EXISTS `projects_info` (
  `proj_id` mediumint(9) NOT NULL auto_increment,
  `proj_code` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `start_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `end_date` datetime NOT NULL default '0000-00-00 00:00:00',
  `status` enum('-1','1','2') NOT NULL default '-1' COMMENT '''-1''=Pending,''1''=Active,''2''=Inactive',
  `code_ho` varchar(255) default NULL,
  `comp_id` tinyint(4) NOT NULL default '1',
  PRIMARY KEY  (`proj_id`),
  KEY `comp_id` (`comp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=363 ;

CREATE TABLE IF NOT EXISTS `purchasing_data` (
  `pur_id` int(11) NOT NULL auto_increment,
  `artlist_codes_id` mediumint(9) NOT NULL,
  `proj_id` mediumint(9) default NULL,
  `pur_to_order` float default NULL,
  `pur_date_needed` date default NULL,
  `pur_foreman` varchar(255) default 'Helpu2',
  `pur_comments` text,
  `pur_type` enum('1','2','3') NOT NULL COMMENT '''1''=Stocks Data, ''2''=CML, ''3''=WO',
  `pur_from_id` int(11) default NULL COMMENT 'ID-ul liniei din tabelul din care vine(central_data / workorder).',
  `pur_item_nr` text,
  `pur_supplier` varchar(2) default NULL,
  `pur_date_sys` datetime NOT NULL,
  `pur_user` varchar(255) NOT NULL,
  `comp_id` tinyint(4) NOT NULL default '1',
  `pur_order_status` enum('1','2','3','4','5','6') NOT NULL default '1' COMMENT '''1''=pending, ''2''=ordered, ''3''=canceled,''4''=modified,''5''=stock, ''6''=solved',
  `pur_status` enum('-1','1') NOT NULL default '1' COMMENT '''-1''=Deleted,''1''=Active',
  PRIMARY KEY  (`pur_id`),
  KEY `artlist_codes_id` (`artlist_codes_id`),
  KEY `proj_id` (`proj_id`),
  KEY `pur_from_id` (`pur_from_id`),
  KEY `comp_id` (`comp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11345 ;


CREATE TABLE IF NOT EXISTS `purchasing_orders` (
  `puo_id` int(11) NOT NULL auto_increment,
  `puo_regnr` int(11) default NULL,
  `pur_id` mediumint(9) NOT NULL,
  `contacts_id` smallint(6) NOT NULL,
  `puo_ordered` double NOT NULL,
  `puo_last_order` double default '0' COMMENT 'Last ordered quantity',
  `puo_date_ordered` date NOT NULL,
  `puo_date_expected` date NOT NULL default '0000-00-00',
  `puo_price` double default NULL,
  `puo_arrived` double default NULL,
  `puo_date_arrived` date default NULL,
  `puo_order_status` enum('-1','1') NOT NULL default '-1' COMMENT '''-1''=not ordered,''1''=ordered',
  `puo_status` enum('-1','1') NOT NULL default '1' COMMENT '''-1''=Deleted,''1''=Active',
  `puo_date_sys` datetime NOT NULL,
  `puo_user` varchar(255) NOT NULL,
  PRIMARY KEY  (`puo_id`),
  KEY `pur_id` (`pur_id`,`contacts_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4327 ;

CREATE TABLE IF NOT EXISTS `stocks_data` (
  `stocks_id` mediumint(9) NOT NULL auto_increment,
  `artlist_codes_id` mediumint(9) NOT NULL,
  `proj_id_in` smallint(6) NOT NULL,
  `proj_id_out` smallint(6) default NULL,
  `invoice_id` mediumint(9) NOT NULL,
  `unit_price` double default NULL,
  `cant_in` float NOT NULL,
  `cant_out` float default '0',
  `stock_type` varchar(5) NOT NULL,
  `zone` varchar(20) default NULL,
  `status` enum('0','1') NOT NULL default '1' COMMENT '''0''=deleted,''1''=active',
  `comments` text,
  `date_sys` datetime default NULL,
  `comp_id` tinyint(4) NOT NULL default '0',
  `username` varchar(255) NOT NULL,
  PRIMARY KEY  (`stocks_id`),
  KEY `artlist_codes_id` (`artlist_codes_id`),
  KEY `invoice_id` (`invoice_id`),
  KEY `proj_id_in` (`proj_id_in`),
  KEY `proj_id_out` (`proj_id_out`),
  KEY `comp_id` (`comp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=71022 ;



Am o problema cu timpul de incarcare al query-ului:

Cod:

SELECT purchasing_data.pur_id, ( DATEDIFF( purchasing_data.pur_date_needed, CURDATE( ) ) - artlist_supp.buffer_time - MAX( artlist_supp.delivery_time ) ) AS pur_ttl, projects_info.proj_code, artlist_codes.artcode, artlist_codes.unit_ro, artlist_codes.desc_ro, SUM( stocks_data.cant_in - stocks_data.cant_out ) AS in_stock, purchasing_data.pur_to_order, purchasing_data.pur_comments, projects_info.proj_id, stocks_data.artlist_codes_id, artlist_codes.stock_min, artlist_codes.stock_max, purchasing_data.pur_date_needed, artlist_codes.artlist_codes_id, purchasing_data.pur_from_id, purchasing_data.pur_type, SUM(purchasing_orders.puo_arrived) AS puo_arrived, SUM(purchasing_orders.puo_last_order) as puo_ordered, purchasing_data.pur_order_status, purchasing_data.pur_item_nr, purchasing_data.pur_supplier, MAX(purchasing_orders.puo_date_expected) AS puo_date_expected, purchasing_data.pur_foreman
FROM purchasing_data
LEFT JOIN purchasing_orders USING ( pur_id )
LEFT JOIN artlist_codes USING ( artlist_codes_id )
LEFT JOIN projects_info USING ( proj_id )
LEFT JOIN stocks_data USING ( artlist_codes_id )
LEFT JOIN artlist_supp USING ( artlist_codes_id )
WHERE purchasing_data.comp_id = 1 AND purchasing_data.pur_status='1' AND (purchasing_data.pur_order_status='1' OR purchasing_data.pur_order_status='4') AND purchasing_data.pur_date_needed IS NOT NULL
GROUP BY purchasing_data.pur_id
ORDER BY pur_ttl ASC, stocks_data.artlist_codes_id ;



Puteti sa imi dati niste sfaturi de optimizare ?

[cristi]

Ai linkuri despre optimizare aici:
http://www.phpromania.net/mysql/0/0.html

[asavin]

Am incercat toate optimizarile posibile ... nu exista alta optiune ?

[Agkelos]

Ai siteul pe un server shared ? S-ar putea sa dureze mult din cauza restrictiilor impuse de administrator. De obicei se limiteaza marimea bufferelor pentru a elimina problemele care apar datorita userilor cu query-uri lungi - cum e al tau. Intreaba suportul tehnic al firmei la care ai hosting.

[asavin]

Nu e pe server shared. E la 10m de mine, in alta camera. Poate nu e bine configurat MySQL-ul. Stiti cumva cam ce setari se pot face?
Sunt disperat ca a ajuns la o asa dimensiune baza de date incat nu se mai incarca datele in pagina ... dureaza prea mult.

[vectorialpx]

Agkelos a zis daca e pe alta masina... masina aia poti sa o tii si in brate, ca tot restrictionata e...

dar, ai destul cod acolo... sigur ai urmat sfaturile de optimizare?

[Pirahna]

si eu am avut probleme cu query-uri de 120 de secunde (bine, ca returnau 12000 de rezultate) ...
problema vine tot de la tine ... ai grija sa fie keys peste tot, sa fie index-uri, selectul sa aleaga numai ce ai nevoie ...

poti sa incerci sa separi selectul in bucati si sa vezi pana cand merge repede, acolo unde incepe sa mearga incet incepi si tu sa optimizezi.

deci solutia tot tu o gasesti, simplificand selectul si adaugand la el pana nu mai merge cum trebuie.

[asavin]

Hai ca am reusit.

Intradevar problema era de la mine...

Am citit o gramada de tutoriale de optimizare in weekend, pe care le-am si implementat fara rezultate prea mari.

Azi dimineata am reusit insa in 5 min sa imi optimizez toata aplicatia citind :

http://20bits.com/2007/04/10/10-tips-for-optimizing-mysql-queries-that-dont-suck/

si mai exact pasajul :

Citat: MySQL will indeed use a dynamic row format if it contains variable length fields like TEXT or BLOB, which, in this case, means sorting needs to be done on disk.

Am transformat (temporar) toate campurile `text` in `varchar`(255) ... pe unde am putut, urmand sa le separ in tabele diferite.

In concluzie nu mai introduc in acelasi tabel campuri `text` sau `blob` ci in tabele separate legate prin fk-uri.

Am redus prin chestia asta timpii de la ~60sec la ~6-8sec query-ul ala ...
Plus ... am transformat din InnoDB in MyISAM tabele pe care se face mult SELECT si mai putin INSERT ... asa am inteles ca e bine ... sa imi ziceti daca gresesc ...

Multumesc,
Alex

[PHPRomania Bot]