Re: Cakephp sql

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 cake-php@googlegroups.com.
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com.
Visit this group at http://groups.google.com/group/cake-php?hl=en-US.