I already fixed this issue, it has something to do with the encoding format, I had do change explicitly CAST(`HitsLog`.`region_id` as CHAR) to CAST(`HitsLog`.`region_id` as CHAR CARACTER SET latin1), I knew that both tables had different encodings utf8 and latin1.
On Thursday, September 6, 2012 11:05:54 AM UTC-6, Zordon wrote: > > Hello everyone, after spending a lot of time trying to find out why cake > php executes queries very slow without success I came here to see if > someone can help me out. > > First this is the query from cakephp sql log and mysql processlist: > > SELECT *, COUNT(*) as hits FROM `hits_log` AS `HitsLog` LEFT JOIN > `countries` AS `Country` ON (`HitsLog`.`country_id` = `Country`.`countryID` > AND CAST(`HitsLog`.`country_id` as CHAR) = `Country`.`countryID`) LEFT JOIN > `regions` AS `Region` ON (`HitsLog`.`country_id` = `Region`.`countryID` AND > CAST(`HitsLog`.`region_id` as CHAR) = `Region`.`regionID` AND > CAST(`HitsLog`.`country_id` as CHAR) = `Region`.`countryID`) WHERE > `HitsLog`.`date` BETWEEN '2012-08-02 00:00:00' AND '2012-09-05 23:59:59' > GROUP BY `HitsLog`.`country_id` ORDER BY `hits` DESC; > > The problem is that it runs faster from the CLI about 0.88 to 1.3 seconds > no matter the difference of the dates, in cakephp it takes 20+seconds to > run if the dates difference is greater it will take a lot longer. > I also made a test with a simple mysqli class and it ran in seconds same > as CLI. > > Using cakephp 1.3 database driver set as "mysqli", "mysq" same results, > same user/pass in all tests. > > Some test were made first in the CLI then in the interface with cakephp to > avoid cache false positives. > > > Thanks in advance! > -- You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. Visit this group at http://groups.google.com/group/cake-php?hl=en-US.
