Title: Improve SQL query results when running php app on GAE and DB on Amazon RDS
Tags: php;sql;google-app-engine;pdo;amazon-rds
Question: Here is something that hit me and wanted to know if I was right or if it could be done better? I am currently running the PHP part on GAE and use Amazon RDS since it is cheaper than google cloud SQL. And also since PHP on GAE does not have native api for Datastore. I know there is a work around but hey this is simpler and I bet a lot of others want their GAE app to sync with their DB than move the who stuff.
I run two queries
This is a join statement that runs when the page loads
$STH = $DBH->prepare("SELECT .....a few selected colmns with time coversion.....
List of Associates.Supervisor FROM Box Scores INNER JOIN
List of Associates ON Box Scores.Initials = List of
Associates.Initials WHERE str_to_date(Date, '%Y-%m-%d') BETWEEN
'{$startDate}' AND '{$endDate}' AND Box Scores.Initials LIKE
'{$initials}%' AND List of Associates.Supervisor LIKE'{$team}%'
GROUP BY Login");
What I get I calculate and then display as a table with each username as link
echo("<td &gt;<a href=# class=\"lightbox\" id =\"$row[0]\"&gt;$row[0]</a&gt;</td&gt;");
So when some one clicks on this link it will call another PHP and using AJAX to display the output I run the second query
2.Second query. This time I am getting everything.
$STH = $DBH-&gt;prepare("SELECT * FROM `Box Scores` INNER JOIN `List of Associates` ON
`Box Scores`.`Initials` = `List of Associates`.`Initials`
WHERE str_to_date(`Date`, '%Y-%m-%d') BETWEEN '{$startDate}' AND '{$endDate}'
AND `V2 Box Scores`.`Initials` LIKE '{$Agent}%'
AND `List of Associates`.`Supervisor` LIKE '{$team}%'");
The output I display in a small pop up as a light box after formatting the output as a table.
I find that the first query to be faster. So it got me thinking should I do something to the second part to make it faster.
Would only selecting the needed columns make it faster. OR should I do a SELECT * FROM as the first and then save it all to a unique file in Google bucket and then make the corresponding SELECT calls from that file?
I trying to make it such that it scale and not slow then when the query has to go through tens of thousands of rows in the DB. The above Queries are executed using PDO or PHP Data Objects.
so what are your thoughts?


Here is another answer: Amazon Red Shift stores each column in a separate partition -- something called a columnar database or vertical partitioning.  This results in some unusual performance issues.
For instance, I have run a query like this on a table will hundreds of millions of row, and it took about minute to return:
select *
from t
limit 10;
On the other hand, a query like this would return in a few seconds:
select count(*), count(distinct field)
from t;
This takes some getting used to.  But, you should explicitly limit the columns you refer to in the query to get the best performance on Amazon (and other columnar databases).  Each additional referenced column requires reading in that data from disk to memory.
Also, limiting the number of columns also reduces the I/O needed to the application.  This can be significant, if you are storing wide-ish data in some of the columns, and you don't use the data.
Comment for this answer: No Red Shift is NOT the same as RDS. RDS is a relational database service similar to MySQL (http://aws.amazon.com/rds/), whereas Red Shift is a data warehouse service using columnar storage is optimised for datasets ranging from a few hundred gigabytes to a petabyte or more (http://aws.amazon.com/redshift/).
Comment for this answer: Is Red Shift the same as RDS ? All the data I have are stored in two tables.
Comment for this answer: Sorry if I was not clear enough. I never heard of Redshift before now. I will look it up.
Would any one know about RDS in the mean time?
Comment for this answer: It does make sense if RDS is running on RedShift since both belong to amazon.
Comment for this answer: @AbilashAmarasekaran . . . I simply assumed the question was about RedShift, because no other database was specified.
Comment for this answer: @AbilashAmarasekaran . . . RDS is a cloud service that gives access to multiple databases (you can read about it as http://aws.amazon.com/rds/).  There is no specific database; you have to provide that information.  Based on the behavior, though, I'm guessing that you are using a columnar database.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:01/17/14', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<php><sql><google-app-engine><pdo><amazon-rds>', 'Title': 'Improve SQL query results when running php app on GAE and DB on Amazon RDS', 'ViewCount': 126, 'id': 21176505, 'user_AboutMe': '<p>Living life and having fun living.</p>\n<p>Love to Game and write code to help people use tech better.</p>\n<p>Looking for new opportunities to continuously learn and do better.</p>\n', 'user_AccountId': 2206069.0, 'user_DisplayName': 'Abilash Amarasekaran', 'user_Reputation': 785.0, 'user_Views': 103.0, 'user_id': 1949257.0}}