XPDO nested joins slow

81 Posts

Dunnock Reply #21, 13 years, 3 months ago

Exactly, the quoted value (not bold) is the index name which and the bold is the field name.

Simple tutorial http://www.mysqltutorial.org/mysql-create-drop-index.aspx but not that specific. Indexes can be one of the hard parts of relation databases. But that gives some pointers. The net is full of tutorials and if you want really delve into them. When googling, if the article is written by mysqlperformanceblog, percona (percona actually keeps the performance blog), skysql and few others you can trust on them. There are lot of bad tutorials also and it is hard to know what is good one.

Just do not create index for each field in database, as that will cause huge index file sizes. Then is better to use multi column indices, but that again is wholly different topic especially with MySQL

126 Posts

johnrobert27 Reply #22, 13 years, 3 months ago

thank you very much for the reply appreciate it laugh

126 Posts

johnrobert27 Reply #23, 13 years, 3 months ago

1  SIMPLE	tblbook	ALL	PRIMARY	NULL	NULL	NULL	2669	Using where; Using temporary; Using filesort
1	SIMPLE	sometbl	ref	system_product_id,system_section_id	system_product_id	9	modx_profile.tblbook.system_product_id	1	Using where
1	SIMPLE	sometbl	ref	system_brand_id	system_brand_id	9	modx_profile.tblbook.system_brand_id	1	Using where
1	SIMPLE	tblbooks	ALL	NULL	NULL	NULL	NULL	2669	Using where; Using join buffer

another question/problem

is it ok the tblbook column 'key' is null?
when i execute this code one of the data did not appear this should appear 3 result but its not
something wrong with the(bold)part of the code those code I add to the code earlier..

snippet code

<?php
$path = MODX_CORE_PATH . 'components/modx_tblbook/';
$result = $modx->addPackage('modx_tblbook',$path . 'model/','modx_');

$divider = "/";

if ((isset($_GET['comid'])!=null)||($_GET['comid'])!=0){
$urlresult = $_GET['comid'];
}

$query = $modx->newQuery('Tblbook');
$query->select(array (
'Tblbook.system_product_id',
'Tblbook.system_brand_id',
'Tblbook.brand_name',
'Tbooks.content',
'Tblbook.company',
'Tblbook.form_class',
'Tgen.system_generic_id',
'Tsec1.section1_id',
'Tsec2.section2_id',
'Tsec3.section3_id',
'Tsec4.section4_id'

));

$query->innerJoin('Tblbooks', 'Tbooks', array("Tblbook.system_brand_id = Tbooks.system_brands_id"));
$query->innerJoin('Tblbrandgenericgroupsms','Tgen',array("Tblbook.system_brand_id = Tgen.system_brand_id"));
$query->innerJoin('Tblproductsections', 'Tprodsec', array(" Tblbook.system_product_id = Tprodsec.system_product_id"));
$query->leftJoin('Tblsection', 'Tsec', array(" Tprodsec.system_section_id = Tsec.system_section_id"));
$query->leftJoin('Tblsection1', 'Tsec1', array(" Tsec.system_section1_id = Tsec1.system_section1_id"));
$query->leftJoin('Tblsection2', 'Tsec2', array(" Tsec.system_section2_id = Tsec2.system_section2_id"));
$query->leftJoin('Tblsection3', 'Tsec3', array(" Tsec.system_section3_id = Tsec3.system_section3_id"));
$query->leftJoin('Tblsection4', 'Tsec4', array(" Tsec.system_section4_id = Tsec4.system_section4_id"));
$query->where(array("Tblbook.system_company_id" => "$urlresult"));
$query->groupBy('brand_name');
$query->sortBy('brand_name','ASC');
$products = $modx->getCollection('Tblbook',$query);

foreach($products as $product){

some code here......
return $output;

mysql code

select tblbook.system_product_id,
tblbook.system_brand_id,
tblbook.brand_name,
tblbooks.content,
tblbook.company,
tblbook.form_class,
tblsection1.section1_id,
tblsection2.section2_id,
tblsection3.section3_id,
tblsection4.section4_id,
tblbrandgenericgroupsms.system_generic_id

From tblbook
INNER Join tblbooks ON tblbook.system_brand_id = tblbooks.system_brands_id
INNER JOIN tblproductsections ON tblbook.system_product_id = tblproductsections.system_product_id
INNER JOIN tblbrandgenericgroupsms ON tblbook.system_brand_id = tblbrandgenericgroupsms.system_brand_id
LEFT JOIN tblsection ON tblproductsections.system_section_id = tblsection.system_section_id
LEFT JOIN tblsection1 ON tblsection.system_section1_id = tblsection1.system_section1_id
LEFT JOIN tblsection2 ON tblsection.system_section2_id = tblsection2.system_section2_id
LEFT JOIN tblsection3 ON tblsection.system_section3_id = tblsection3.system_section3_id
LEFT JOIN tblsection4 ON tblsection.system_section4_id = tblsection4.system_section4_id
WHERE tblbook.system_company_id = '20100106' GROUP BY tblbook.brand_name;

[ed. note: johnrobert27 last edited this post 13 years, 3 months ago.]