Downloading Historical Data

New members questions
Post Reply
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

mysql
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

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.
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

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.
Roberto.
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

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.
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.


Sent from my iPad mini
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

How did you structure your db?
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

I'm not very familiar with optimizations of mysql but have you tried adding an index?

Code: Select all

ALTER TABLE 'tags' ADD INDEX 'name' ('name')
To remove index:

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.
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

rimai wrote:How did you structure your db?
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.


Sent from my iPad mini
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

it could even be your script to generate the data to be sent

Sent from my Moto X
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

phrusher wrote:I'm not very familiar with optimizations of mysql but have you tried adding an index?

Code: Select all

ALTER TABLE 'tags' ADD INDEX 'name' ('name')
To remove index:

Code: Select all

ALTER TABLE 'tags' DROP INDEX 'name'
Yes, I already have an index for column name on table tags
Roberto.
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

Try it again now??
Did it improve?
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

Hell yeah! What did you do? All json request are now roughly 400ms :)
rimai
Posts: 12881
Joined: Fri Mar 18, 2011 6:47 pm

Re: Downloading Historical Data

Post by rimai »

I guess there was a setting on MySQL that was overlooked since the beginning :oops:
Roberto.
phrusher
Posts: 65
Joined: Fri May 25, 2012 12:22 am

Re: Downloading Historical Data

Post by phrusher »

Good thing you found it. Great work!
binder
Posts: 2871
Joined: Fri Mar 18, 2011 6:20 pm
Location: Illinois
Contact:

Re: Downloading Historical Data

Post by binder »

rimai wrote:I guess there was a setting on MySQL that was overlooked since the beginning :oops:
that's awesome you got it figured out. better late than never. :)
User avatar
lnevo
Posts: 5430
Joined: Fri Jul 20, 2012 9:42 am

Re: Downloading Historical Data

Post by lnevo »

Awesome!
Post Reply