XPDO nested joins slow

81 Posts

Dunnock Reply #11, 13 years, 3 months ago

Could you copy paste the explain results?

Also you can run "SHOW CREATE TABLE <tablename>" (replace <tablename> with table name of the row which has all and null) and copy paste that result also. The schema you pasted is actually the modx schema and it should not hold you data.

126 Posts

johnrobert27 Reply #12, 13 years, 3 months ago

here is the result of the show create table

result of the 'explain'

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tblproductsections	ALL	NULL	NULL	NULL	NULL	2845	Using temporary; Using filesort
1	SIMPLE	tblbooks	ALL	NULL	NULL	NULL	NULL	2669	Using join buffer
1	SIMPLE	tblbook	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblproductsections.system_product_id	1	Using where
1	SIMPLE	tblsection	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblproductsections.system_section_id	1	 
1	SIMPLE	tblsection1	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblsection.system_section1_id	1	 
1	SIMPLE	tblsection2	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblsection.system_section2_id	1	 
1	SIMPLE	tblsection3	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblsection.system_section3_id	1	 
1	SIMPLE	tblsection4	eq_ref	PRIMARY	PRIMARY	8	modx_profile.tblsection.system_section4_id	1

first table

CREATE TABLE `tblproductsections` (
 `system_product_id` double DEFAULT NULL,
 `system_section_id` double DEFAULT NULL,
 `short_list_info` int(11) DEFAULT NULL,
 `date_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `updated_by` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

second table

CREATE TABLE `tblbooks` (
 `system_product_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `system_brands_id` longtext NOT NULL,
 `system_edition_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `system_brand_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `system_distributor_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `system_manufacturer_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `system_principal_id` double(15,0) unsigned NOT NULL DEFAULT '0',
 `company_texting` varchar(255) DEFAULT '',
 `company` varchar(255) DEFAULT NULL,
 `company_ppdr` varchar(255) DEFAULT '',
 `use_address` int(1) unsigned DEFAULT '0',
 `system_company_id` double(15,0) unsigned DEFAULT '0',
 `form_class` varchar(255) DEFAULT NULL,
 `generics` varchar(255) DEFAULT NULL,
 `content` longtext,
 `new_section` tinyint(1) unsigned DEFAULT '0',
 `disease` longtext,
 `long_indication` longtext,
 `short_indication` longtext,
 `general_dose` longtext,
 `adult_dose` longtext,
 `pedia_dose` longtext,
 `elderly_dose` longtext,
 `packaging` longtext,
 `contra_indications` longtext,
 `spc_contra_indications` longtext,
 `precautions` longtext,
 `spc_precautions` longtext,
 `side_effects` longtext,
 `spc_side_effects` longtext,
 `drug_interactions` longtext,
 `spc_drug_interactions` longtext,
 `system_pregnancy_id` double(15,0) unsigned DEFAULT '0',
 `system_lactation_id` double(15,0) unsigned DEFAULT '0',
 `system_metabolism_id` double(15,0) unsigned DEFAULT '0',
 `description` longtext,
 `uses` longtext,
 `sensitivity` longtext,
 `directions` longtext,
 `instrument` longtext,
 `withdrawal` longtext,
 `storage` longtext,
 `show_short_list_info` tinyint(1) unsigned DEFAULT '0',
 `registration_number` varchar(50) DEFAULT NULL,
 `date_expiry` datetime DEFAULT NULL,
 `form_class_sms` varchar(255) DEFAULT NULL,
 `form_class_palm` varchar(255) DEFAULT NULL,
 `generics_sms` varchar(255) DEFAULT NULL,
 `generics_palm` varchar(255) DEFAULT NULL,
 `content_sms` longtext,
 `content_palm` longtext,
 `long_indication_sms` longtext,
 `long_indication_palm` longtext,
 `general_dose_sms` longtext,
 `general_dose_palm` longtext,
 `adult_dose_sms` longtext,
 `adult_dose_palm` longtext,
 `pedia_dose_sms` longtext,
 `pedia_dose_palm` longtext,
 `packaging_sms` longtext,
 `packaging_palm` longtext,
 `contra_indications_sms` longtext,
 `contra_indications_palm` longtext,
 `precautions_sms` longtext,
 `precautions_palm` longtext,
 `side_effects_sms` longtext,
 `side_effects_palm` longtext,
 `drug_interactions_sms` longtext,
 `drug_interactions_palm` longtext,
 `description_sms` longtext,
 `description_palm` longtext,
 `uses_sms` longtext,
 `uses_palm` longtext,
 `sensitivity_sms` longtext,
 `sensitivity_palm` longtext,
 `directions_sms` longtext,
 `directions_palm` longtext,
 `instrument_sms` longtext,
 `instrument_palm` longtext,
 `withdrawal_sms` longtext,
 `withdrawal_palm` longtext,
 `not_listed` tinyint(1) unsigned DEFAULT '0',
 `phased_out` tinyint(1) unsigned DEFAULT '0',
 `delisted` tinyint(1) unsigned DEFAULT '0',
 `sms` tinyint(1) unsigned DEFAULT '0',
 `notfdlist` tinyint(1) unsigned DEFAULT NULL,
 `tfdonly` tinyint(1) unsigned DEFAULT NULL,
 `deleted` tinyint(1) unsigned DEFAULT '0',
 `dangerous` int(1) unsigned DEFAULT '0',
 `sms_no_show` int(1) unsigned DEFAULT '0',
 `date_entered` datetime DEFAULT NULL,
 `entered_by` varchar(50) DEFAULT NULL,
 `date_modified` datetime DEFAULT NULL,
 `modified_by` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`system_product_id`),
 FULLTEXT KEY `company_texting` (`company_texting`,`content`),
 FULLTEXT KEY `company_texting_2` (`company_texting`,`content`),
 FULLTEXT KEY `company_texting_3` (`company_texting`,`content`),
 FULLTEXT KEY `content` (`content`,`long_indication`),
 FULLTEXT KEY `generics` (`generics`,`general_dose`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

sorry where should I find the xpdo schema? [ed. note: johnrobert27 last edited this post 13 years, 3 months ago.]

81 Posts

Dunnock Reply #13, 13 years, 3 months ago

Quickly could say that you need indexes for next columns: Tblproductsections.system_product_id, Tblproductsections.system_section_id and tblbooks.system_company_id. But just by reading those are not enough to be honest. Loads of id columns in the tables and no idea how many of them will be used with your queries. But those affect the query directly.

You can add the indexes in next manner using phpmyadmin:

CREATE INDEX system_product_id ON Tblproductsections (system_product_id);
CREATE INDEX system_section_id ON Tblproductsections (system_product_id);
CREATE INDEX system_company_id ON tblbooks (system_company_id);

The index names (after INDEX) could be other also, as long they do not exists in the tables already (they do not). If the tables are big, that operation can take a good while as it will create the necessary indexes. The schema could reside in the /core/components/yourComponent/schema/ but that is just "standard" but it can be anywhere really. You might be able to see it if you have addPackage or getService command within the code to point to some path.

126 Posts

johnrobert27 Reply #14, 13 years, 3 months ago

thank you very much that solve the problem laugh

cheers

81 Posts

Dunnock Reply #15, 13 years, 3 months ago

You'll most likely will encounter lot of same kinds of situations with the indexes. You can use the 'explain' command to guide you. The 'possible_keys' also shows if there is an index you can use and view the SHOW create table which column the index points to. This is not always easy but it gives lot of guidance to right direction.

81 Posts

Dunnock Reply #16, 13 years, 3 months ago

Oh yeah, you can also show all indexes with "SHOW INDEXES FROM <tablename>", it is lot shorter and faster to read than 'SHOW CREATE TABLE <tablename>'

126 Posts

johnrobert27 Reply #17, 13 years, 3 months ago

thanks for the advised i will definitely used when i encounter these problem laugh

5,888 Posts

Bruno17 Reply #18, 13 years, 3 months ago

I'm glad you got it working faster by properly indexing now.

sorry where should I find the xpdo schema?

components/modx_tblbook/model/schema/

isn't it there?

-------------------------------

you can buy me a beer, if you like MIGX

http://webcmsolutions.de/migx.html

Thanks!

126 Posts

johnrobert27 Reply #19, 13 years, 3 months ago

yes the one i pasted earlier thats what i found in my components/modx_tblbook/model/schema/

126 Posts

johnrobert27 Reply #20, 13 years, 3 months ago

CREATE INDEX 'system_product_id' ON Tblproductsections (system_product_id);
CREATE INDEX 'system_section_id' ON Tblproductsections (system_product_id);
CREATE INDEX 'system_company_id' ON tblbooks (system_company_id);

quetion the indexes inside the '' can it be any index i want?
and the one in bold face is the field in the table?

for example i will add another index

CREATE INDEX 'system_anything_id' ON tblbooks (system_company_id);