Executing huge queries(60,000rows +)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Executing huge queries(60,000rows +)

Post by VladSun »

Make sure that ALL fields used in WHERE and ORDER BY clauses have theoir indexes build.
You could even try building composite indexes for cases where more than one filed is used in these clauses.
Last edited by VladSun on Wed Aug 05, 2009 8:47 am, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Hey thanks again. I'm using mdb2 right now instead of mysqls built in stuff. I'm using the command result->free(); I thought this was similar to what you were talking about.
Also I think its memory because every time I increase the memory the query gets further and further down the page and more results are displayed than the time before.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Executing huge queries(60,000rows +)

Post by Eran »

The index on pkg_line_id (I think it's called "FK_pkg_load_pkg_line_id" for some reason) needs to be a composite and include time_stamp and year as secondary fields (in that order). Make that modification and run explain again
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Ok. I'm sorry. I set the time limit to 0 and it worked fine. Thank you so much. What should I default it too or where should I actually set it back say if they just exit the browser in the middle of the query. Thanks a lot.

Hey again. Thanks to all who are helping. These are really good tips. Is there a rep system here?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Executing huge queries(60,000rows +)

Post by onion2k »

drewh wrote:Is there a rep system here?
There was, but I broke it by being too awesome. 8)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Executing huge queries(60,000rows +)

Post by Eran »

there is a rep system, if you check the colors of the usernames. The more "green" you are, the more awesome you are
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Haha ok.

Hey guys one more question how would I go about converting all the time stamps to year, month, day , hour columns. It seems a little tricky.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Executing huge queries(60,000rows +)

Post by onion2k »

Add the columns with ALTER TABLE `table` ADD COLUMN `hour` blah blah blah, then do something like "UPDATE `table` SET `hour` = HOUR(`timestamp`), `day` = DAYOFYEAR(`timestamp`), `year` = YEAR(`timestamp`);".
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Executing huge queries(60,000rows +)

Post by Eran »

why would you want to do that though? better to leverage MySQL internal date handling
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Thanks onion I'll try that.
Also onion thought it would cut down on the temp tables and function calls.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Executing huge queries(60,000rows +)

Post by onion2k »

pytrin wrote:why would you want to do that though? better to leverage MySQL internal date handling
When you're fetching 60,000 rows and need to run the function on every row it's a lot faster to have done the transformations beforehand, especially if you're doing the same operation over and over again on the same data.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Executing huge queries(60,000rows +)

Post by Eran »

Right, I missed that. It could help, but you should try to optimize your indexes first. You would gain much more performance from that in my opinion, and it's better to exhaust all other options before denormalizing the schema.
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Thanks a lot guys. Adding the index to the time_stamp made the page load almost instantly. And that's like 60,000 rows. Awesome!
drewh
Forum Newbie
Posts: 12
Joined: Mon Aug 03, 2009 2:05 pm

Re: Executing huge queries(60,000rows +)

Post by drewh »

Also guys. As future reference for people who may look at this topic down the road. Making the functions pre calculated did not drop times by much at all(.1 to .2 seconds off). I did page load calculations and put them into an excel spreadsheet. One with function calls on in one database and in another database with the functions pre calculated. Thanks a lot though.
Post Reply