Downloading Historical Data
Re: Downloading Historical Data
Well, I guess the .NET MySQL driver is not so good either.
I tested it by using the faster sql query and it boggled down on the .NET page.
I guess I'll try php when I get some time.
I tested it by using the faster sql query and it boggled down on the .NET page.
I guess I'll try php when I get some time.
Roberto.
Re: Downloading Historical Data
Duh... Never mind. I had limiting of fetch when I was using MySQL workbench.
If I fetch all the records, it took almost 25s for the first query and it yielded 2607 rows. It took about the same time for the second query and it yielded 106458 rows.
So, back to the MySQL server optimization.
If I fetch all the records, it took almost 25s for the first query and it yielded 2607 rows. It took about the same time for the second query and it yielded 106458 rows.
So, back to the MySQL server optimization.
Roberto.
Re: Downloading Historical Data
my portal is using php. so i have the xml sending working. i can easily create a json version of it to test out. i should be able to have this tomorrow if you guys want me to.rimai wrote:Well, I guess the .NET MySQL driver is not so good either.
I tested it by using the faster sql query and it boggled down on the .NET page.
I guess I'll try php when I get some time.
Sent from my iPad mini
Re: Downloading Historical Data
I'm not very familiar with optimizations of mysql but have you tried adding an index?
To remove index:
Code: Select all
ALTER TABLE 'tags' ADD INDEX 'name' ('name')
Code: Select all
ALTER TABLE 'tags' DROP INDEX 'name'
Last edited by phrusher on Tue Aug 05, 2014 11:01 am, edited 1 time in total.
Re: Downloading Historical Data
i think very similar to yours but im using sqlite3 and not a larger scale db. mine is just a flat file. irs pretty simplistic in terms of structure.rimai wrote:How did you structure your db?
Sent from my iPad mini
Re: Downloading Historical Data
it could even be your script to generate the data to be sent
Sent from my Moto X
Sent from my Moto X
Re: Downloading Historical Data
Yes, I already have an index for column name on table tagsphrusher wrote:I'm not very familiar with optimizations of mysql but have you tried adding an index?
To remove index:Code: Select all
ALTER TABLE 'tags' ADD INDEX 'name' ('name')
Code: Select all
ALTER TABLE 'tags' DROP INDEX 'name'
Roberto.
Re: Downloading Historical Data
Hell yeah! What did you do? All json request are now roughly 400ms
Re: Downloading Historical Data
I guess there was a setting on MySQL that was overlooked since the beginning
Roberto.
Re: Downloading Historical Data
Good thing you found it. Great work!
Re: Downloading Historical Data
that's awesome you got it figured out. better late than never.rimai wrote:I guess there was a setting on MySQL that was overlooked since the beginning