modx vs php

126 Posts

johnrobert27 Reply #1, 13 years, 2 months ago

Hi there

I want to ask if there is any differences on how modx handle tables,queries,databases etc. vs. on how php handle this stuff??

because I'm really confused with it I'll give you some example.

In php MySQL 'LIKE' with joins of different tables(at least 5 tables) is running good the fetching of data is not that slow and depends also on how many data it retrieves

While in modx this method is running slow in retrieving data from the table.

why is this happen??

thanks!

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 13 years, 2 months ago

MODx uses xPDO, which is a library of extensions to PHP's PDO library. Any object-oriented abstraction of raw functions will be slower than the raw PHP functions. There is a trade-off between the purpose of an ORB and the speed of hand-coding raw code. Complex queries may well be better hand-coded, speed-wise, as you have discovered.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

24,544 Posts

BobRay Reply #3, 13 years, 2 months ago

You can also use PDO with MODX, which might be faster than xPDO, but slower than raw PHP functions.

If you want to post your queries, someone here might be able to suggest ways to optimize it in MODX.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

126 Posts

johnrobert27 Reply #4, 13 years, 2 months ago

Thanks for the reply!

here is my code

$query = $modx->newQuery('Tblbook');
        $query->select(array (
            'Tblbook.system_product_id',
            'Tblbook.system_brand_id',
            'Tblbook.brand_name',
            'Tsearch.content',
            'Tblbook.company',
            'Tblbook.form_class',
            'Tgen.system_generic_id',
            'Tsec1.section1_id',
            'Tsec2.section2_id',
            'Tsec3.section3_id',
            'Tsec4.section4_id'
 
        ));
    
        $query->innerJoin('Tablesearch', 'Tsearch', array("Tblbook.system_brand_id = Tsearch.system_brand_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(
        array(
            "Tsearch.brand_name:LIKE" => "%$xkeywordx%",
            "OR:Tsearch.company:LIKE" => "%$xkeywordx%",
        array(
            "OR:Tsearch.content:LIKE" => "%$xkeywordx%",
            "OR:Tsearch.long_indication:LIKE" => "%$xkeywordx%",
        ),
        ),
        ));
        $query->groupBy('brand_name');
        $query->limit('50');
        $products = $modx->getCollection('Tblbook',$query);

I've also try this one but same slow results and also data display is not that accurate

$search = $modx->quote($xkeywordx, PDO::PARAM_STR);
$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',
            "MATCH(Tblbook.brand_name, Tblbook.company)AGAINST ($search IN BOOLEAN MODE)",
            "MATCH(Tbooks.content, Tbooks.long_indication) AGAINST ($search IN BOOLEAN MODE)"
        ));
       
        $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("MATCH(Tblbook.brand_name, Tblbook.company)AGAINST ($search IN BOOLEAN MODE)",
        "MATCH(Tbooks.content, Tbooks.long_indication) AGAINST ($search IN BOOLEAN MODE)")
        );
        $query->groupBy('brand_name');
        $query->limit('50');
        $products = $modx->getCollection('Tblbook',$query);

81 Posts

Dunnock Reply #5, 13 years, 2 months ago

Oh yeah, make sure that you select the primary key field of Tblbook. Otherwise getCollection will #1 be really slow #2 die unexpected

Also, read the link from mysql page I posted in other thread for boolean searches. There are loads of options there you can use to improve accuracy of search. And like said, %search% cannot use indexes. Also joins always have impact on speed, the query optimizer has to go huge number of possibilities for your query.

xPDO itself is not slow, the queries run pretty much with same speed as with normal PDO. The actual MySQL query does not differ at all from PDO query, the processing of method might take few milli- or microseconds more than without it.

126 Posts

johnrobert27 Reply #6, 13 years, 2 months ago

it still slow? this problem giving a headache for almost a month still no luck sad