Help speeding up loading data

[Nikidala]

I am trying to load / grab / select around 200,000 records from a table via PHP at it is taking on average 8 seconds. I am relatively sure that it is the actually run / load time rather than just a slow connection between me and the server because I was outputting the microtime before and after the loading.

I was also running it at the slowest time for my server (slowest meaning not being hit very often). I have tried with and without indexes on the id field, and that made very little impact, so it made me think even more that it was a PHP issue (rather than a database one). Also, I rebooted the server during testing to see if that would help (hey, it works on Windows machines, so why not try)...

I really need to get this down, but can't figure out how to optimize it any more than I already have.

Server Details:
PHP 4.2.2
MySQL 3.23.54

Table Details:
spell_langid = smallint (index)
hash = int
flags = int

Total rows size = 17 bytes
# rows = 268,449
# rows wanted = ~200,000

PHP:

PHP Code:


            $words=$DB_site->query("SELECT hash, flags
                                    FROM spell_hash
                                    WHERE spell_langid = $lang_id");

            while (list($hash, $flags) = $DB_site->fetch_array($words)) {
                $this->hashes["$hash"] = $flags;
            } 


I have already tried it via files, but the database was quicker than files... Anybody have any ideas? Anybody loading this amount of data at the same time?


Thanks!
Nikki

[filburt1]

Don't load so many, that's the only solution. Not only may the query take forever but it will use a huge amount of memory to cache all the data into variables.

[Nikidala]

That is what I was afraid of hearing... Oh well, that is what I was thinking, but was hoping that someone might have some other insight for me... Poopy...

Okie dokie, I will be working on an alternate way... Thanks for the quick response and help!

TTYL,
Nikki

[feldon23]

The best solution in these situations is to post here what you are trying to accomplish, not just the trouble you have bumped into with one chunk of PHP code.

Often there is a less-intensive solution for the whole task.