SELECT 
	l.idgeo_location as "id",
	l.lat as "lat",
	l.lon as "lon",
	l.country as "country",
	l.region as "region",
	l.city as "city",
	count(distinct d.requestid) as "requestN",
	count(distinct d.user_id) as "userN",
	sum(IF(d.result=0,1,0)) as "fileN",
	sum(IF(d.result=12,1,0)) as "duplicateN",
	sum(IF(d.result<>12 and d.result <> 0,1,0)) as "errorN"

FROM
	geo_locations l 
	INNER JOIN ap_files d ON (l.idgeo_location = d.fk_idgeo_location)
		
WHERE d.ts between '2014-08-01' and '2014-08-22'
GROUP BY l.idgeo_location

SELECT 
	l.idgeo_location as "id",
	l.lat as "lat",
	l.lon as "lon",
	l.country as "country",
	l.region as "region",
	l.city as "city",
	count(distinct d.requestid) as "requestN",
	count(distinct d.user_id) as "userN",
	sum(IF(d.result=0,1,0)) as "fileN",
	sum(IF(d.result=12,1,0)) as "duplicateN",
	sum(IF(d.result<>12 and d.result <> 0,1,0)) as "errorN"

FROM
	(select distinct fk_idgeo_location 
         from ap_files 
         where ts between '2014-08-01' and '2014-08-22' 
         order by fk_idgeo_location 
         limit 1 offset 0) x

	INNER JOIN 
	geo_locations l on(x.fk_idgeo_location = l.idgeo_location)
	INNER JOIN ap_files d ON (x.fk_idgeo_location = d.fk_idgeo_location)
		
WHERE d.ts between '2014-08-01' and '2014-08-22'
GROUP BY l.idgeo_location

(select distinct fk_idgeo_location 
         from ap_files 
         where ts between '2014-08-01' and '2014-08-22' 
         order by fk_idgeo_location 
         limit 1 offset 0) x

$sql_proto = "call sp_my(:limit,:offset, '$data_start, $data_end'")

$limit = 10, $offset = 0;

$sql = str_replace(array(':limit',':offset'),array($limit,$offset),$sql_proto );

$result = Database::query($sql);

while($result->num_rows > 0)
{
      while($row = $result->fetch_row())
     {
          var_dump($row);
     }

     $offset += $limit;
     $sql = str_replace(array(':limit',':offset'),array($limit,$offset),$sql_proto );

     $result = Database::query($sql);
}

CREATE TABLE `ap_files` (
	`idap_file` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`user_id` BINARY(16) NOT NULL,
	`ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`path` VARBINARY(255) NOT NULL,
	`result` TINYINT(4) NOT NULL,
	`requestid` VARCHAR(255) NOT NULL,
	`nodeid` TINYINT(4) NOT NULL,
	`ip` BIGINT(20) NOT NULL DEFAULT '0',
	`fk_idgeo_location` BIGINT(20) NOT NULL DEFAULT '0',
	PRIMARY KEY (`idap_file`),
	INDEX `user_id` (`user_id`),
	INDEX `fk_idgeo_location` (`fk_idgeo_location`),
	INDEX `ts` (`ts`, `fk_idgeo_location`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=634603;


CREATE TABLE `geo_locations` (
	`idgeo_location` BIGINT(20) NOT NULL,
	`country` CHAR(2) NOT NULL,
	`region` CHAR(2) NOT NULL,
	`city` CHAR(60) NOT NULL,
	`postalcode` CHAR(16) NOT NULL,
	`lat` DECIMAL(7,4) NOT NULL,
	`lon` DECIMAL(7,4) NOT NULL,
	`metrocode` INT(11) NOT NULL,
	`areacode` INT(11) NOT NULL,
	PRIMARY KEY (`idgeo_location`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	d	ALL	fk_idgeo_location,ts	\N	\N	\N	624459	Using where; Using temporary; Using filesort
1	SIMPLE	l	eq_ref	PRIMARY	PRIMARY	8	d.fk_idgeo_location	1	\N

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	<derived2>	system	\N	\N	\N	\N	1	\N
1	PRIMARY	l	const	PRIMARY	PRIMARY	8	const	1	\N
1	PRIMARY	d	ref	fk_idgeo_location,ts	fk_idgeo_location	8	const	79520	Using where
2	DERIVED	ap_files	index	fk_idgeo_location,ts	fk_idgeo_location	8	\N	624621	Using where

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ap_files	index	fk_idgeo_location,ts	fk_idgeo_location	8	\N	42	Using where