Re: Working with large datasets

-------------------------------------------------- From: "Jack van Zanen" <jack@vanzanen.com> Sent: Tuesday, October 11, 2011 12:09 PM To: "Jason Pruim" <lists@pruimphotography.com> Cc: "Thompson, Jimi" <jimit@mail.cox.smu.edu>; "Bastien" <phpster@gmail.com>; <php-db@lists.php.net> Subject: Re: [PHP-DB] Working with large datasets

Hi You need to index the right fields. even on a laptop a select from 8 million rows with two rows returned should take a few seconds max only. The first time you run the query the data has to come from disk, second time you run same query you'd expect that data to sit in cache and be very quick. Jack van Zanen ------------------------- This e-mail and any attachments may contain confidential material for the sole use of the intended recipient. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of this e-mail or any attachment is prohibited. If you have received this e-mail in error, please contact the sender and delete all copies. Thank you for your cooperation On Tue, Oct 11, 2011 at 10:39 AM, Jason Pruim <lists@pruimphotography.com>wrote:

Jason Pruim lists@pruimphotography.com On Oct 10, 2011, at 5:27 PM, Thompson, Jimi wrote:

I really think that you should try running it from the command line and

see what the issues are. Get both Apache and php out of the way. I've seen some PHP scripts use up all the file handles (OS limit) even on a 64 bit server when they start doing complex things with data sets.

If it works ok without PHP/Apache then you can start lookig at PHP and

APache.

ISOLATE the issue not complicate it.... My 2 cents,

Hi Jimi, I've done it from the command line a few times, first time I run a simple: SELECT * FROM Main WHERE state="test"; it takes: 2 rows in set (1 min 44.20 sec) after that initial run it's pretty responsive, usually around 0.01 seconds. If I do a select based on the new-york state, which is all of my almost 9 million records after the initial run, it returns it all VERY quickly: 25000 rows in set (0.02 sec) So commandline is running fine after the initial... I'm leaning towards either a problem with web server, PHP setup, or my PHP code... I've used the code many times before but never on such a large dataset... When I pull the pagination out completely.... It's pretty much the same result... fetching the info for new-york works just fine but not "test" One thing I am noticing right now though is the fact that when I switch over to using the test state.... Right now it's not displaying anything... Not even able to view the source... Okay... Enough rambling right now... Need to do some more checking before I can come to a conclusion :) -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php

__________ Information from ESET NOD32 Antivirus, version of virus signature database 6532 (20111010) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 6532 (20111010) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com