MySQL or Google Spreadsheets

[Shane]

I'm currently querying a google spreadsheet directly for my data.
Generally speaking, it works really smoothly. My only issue is that I
haven't worked out how to manipulate the spreadsheet query process.
For example, say I'd like to draw only the data points from the
spreadsheet in which the value in column A is "Boston Red Sox". The
following doesn't work:

query.setQuery('SELECT * WHERE A='Boston Red Sox')

Without the WHERE, it works fine, so the rest of the code is okay. Is
there specific Google spreadsheet syntax that I should be using?

More generally speaking, would the more experienced users suggest that
I use MySQL or stick with Google Spreadsheets. From my understanding,
with MySQL, I'd have to recreate the data table in the html then run
it through google visualizations, whereas querying google spreadsheets
doesn't need that intermediate step of having to recreate the data
table. Is this correct? Are there any speed issues relating to the
MySQL approach if I'm drawing a large data (more than 2500 data
points) then passing it through Visualizations? Am I missing any
functionality by using Google spreadsheets instead of the PHP+MySQL
approach?

Thanks a lot in advance - we beginners appreciate your help.

[NA]

Some thoughts:

> Without the WHERE, it works fine, so the rest of the code is okay. Is
> there specific Google spreadsheet syntax that I should be using?

If there are, that would be one of the drawbacks of this method ;)
Using Google Spreadsheets, other than binding you to potentially
special syntax, means you are specifying your query client-side, where
you may instead want to specify the query parameters server side. I
believe this also implies the spreadsheet is accessible by the client,
too, but I'm not sure. This may not be an issue, but it's worth
keeping in mind.

> From my understanding,
> with MySQL, I'd have to recreate the data table in the html then run
> it through google visualizations

You would have your MySQL query return the rows and columns you want,
and then have your server side code generate the right javascript to
define the table. The GVIZ Team has documented a few different ways
of doing this. One way is described here:

http://code.google.com/apis/chart/interactive/docs/php_example.html

Another is here:

http://code.google.com/apis/chart/interactive/docs/dev/implementing_data_source.html

They each have their own pros and cons; it's best to look over them to
see which makes more sense for your project.

> Are there any speed issues relating to the
> MySQL approach if I'm drawing a large data (more than 2500 data
> points) then passing it through Visualizations?

Once the data has been sent to the browser, where it came from (MySQL
vs a Google Spreadsheet) doesn't really matter. What matters is how
complex your visualizations are, as well as how you are consuming the
data (for example, the above two links show two different ways of
getting data into the tables). That happens client-side, which
affects rendering performance.

If you want to see how the visualizations perform with 2500+ data
points, it's best to make a few test cases and try them out to see.
Note that you need to test in different browsers, since JavaScript
performance varies from browser to browser (even from version to
version). From my experience, no single browser is fastest or slowest
in everything, and the variance can be significant. That's important
to keep in mind; it's one of the "challenges" of building js
applications. It's also one of the headaches ;)

> Am I missing any
> functionality by using Google spreadsheets instead of the PHP+MySQL
> approach?

As far as visualizations go, I don't think PHP/MySQL vs GOOG
spreadsheets matters. These choices are really server-side choices.
You should be able to switch from one to the other without changing
the performance of the visualization part (though the server-side
performance will naturally change).

My general advice is to pick standard technologies as much as
possible, and be careful when using vendor specific tools - build the
right amount of abstraction around them so that you can switch
implementations without a major headache, but still keep your
development time fast. Using Google Spreadsheets has some benefits -
they are spreadsheets that non programmers can edit, fast development
time, etc, but they have some disadvantages. There are pros and cons
with PHP/MySQL, too (standard technologies, but you need to create and
manage databases, write code or manage a framework, need a server for
the data, etc).

Test performance a lot, especially keeping in mind the browser
differences I talked about. I suggest testing Chrome, IE, Firefox,
and Safari. Running your app on an iPad or Blackberry, or Android
device? Test there, too. Each of these browsers and devices will
perform differently, especially with large data sets. Even the type
of visualization matters (how many lines in your chart? how many
slices in your pie? labels? tables with or without pagination? any
animations? etc).

In general, it's not hard to get started, but to make things perform,
it takes a lot of testing and fine tuning...

Good luck!

[Shane]

Thanks a lot for your comprehensive response, NA. I think I will try
to work through with MySQL. While it will almost certainly take me a
little longer, I agree that it's probably best to employ standard
technologies as much as possible. And I certainly don't mean that as a
slight to Google - running the motion chart through the google
spreadsheet worked very well and demonstrated the illustrative power
of the visualization, and it worked surprisingly quickly given the
complexity of the motion chart and the size of the dataset. However,
I'd now like to set it up so that visitors can select the data points,
variables and a few other options in a form of sorts and generate
their own motion charts with my data sets. I think a PHP/MySQL
framework will best facilitate that.

And thanks for the advice regarding browsers - I'll certainly run
tests on all the major browsers.

Regards,
Shane

On Dec 31 2011, 11:00 pm, NA <nabeel.a...@gmail.com> wrote:
> Some thoughts:
>
> > Without the WHERE, it works fine, so the rest of the code is okay. Is
> > there specific Google spreadsheet syntax that I should be using?
>
> If there are, that would be one of the drawbacks of this method ;)
> Using Google Spreadsheets, other than binding you to potentially
> special syntax, means you are specifying your query client-side, where
> you may instead want to specify the query parameters server side.  I
> believe this also implies the spreadsheet is accessible by the client,
> too, but I'm not sure.  This may not be an issue, but it's worth
> keeping in mind.
>
> > From my understanding,
> > with MySQL, I'd have to recreate the data table in the html then run
> > it through google visualizations
>
> You would have your MySQL query return the rows and columns you want,
> and then have your server side code generate the right javascript to
> define the table.  The GVIZ Team has documented a few different ways
> of doing this.  One way is described here:
>
>  http://code.google.com/apis/chart/interactive/docs/php_example.html
>
> Another is here:
>

>  http://code.google.com/apis/chart/interactive/docs/dev/implementing_d...