I've been fighting this issue in different ways for a while. Right now I have cron generate a MySQL view of a joined query processing 300,000 results with a limit of 1000. This query runs very fast.
Here's the query that generates the view;
$sql = "
DROP VIEW IF EXISTS active_users_list;
CREATE VIEW active_users_list AS
SELECT roles_users.*, users.* FROM roles_users
LEFT JOIN users ON users.id = roles_users.user_id
WHERE roles_users.role_id = '2'
AND users.f_name IS NOT NULL
AND users.city IS NOT NULL
AND users.gender IS NOT NULL
AND users.date_of_birth IS NOT NULL
ORDER BY users.last_active DESC
LIMIT 1000;
";
$con = \Phalcon\DI::getDefault()->getShared('db');
if($con->query($sql)) {
echo "Active user list generated\n";
}Then the following code is used to query the view of 1000 results in a paginated way;
$sql = "
select * from active_users_list
LIMIT $offset, $limit
";
$con = \Phalcon\DI::getDefault()->getShared('db');
$data = $con->query($sql);
$data->setFetchMode(\Phalcon\Db::FETCH_OBJ);
$results = (object) $data->fetchAll();This query can be very slow and can take 6 seconds or more!
I originally had the main query running here, paginated, with all 300,000 results, but as you got further to the end of pagination it would get rediculously slow.
It seems to be the LIMIT $offset, $limit which slows it down. I was under the impression the above was a raw SQL query, so would avoid Phalcon's limitations when working with large result sets. Is the speed issue here still caused by Phalcon?
