Help speeding up loading data

[Princess Nikida]

I am not quite sure if this should go in the MySQL section or here, but I am guessing here, as I think it is more of a PHP issue that an SQL issue.

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 really need to get this down, but can't figure out how to optimize it any more than I already have.

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

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

PHP:

            $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... Am I just up a creek without a paddle, or what? Anybody have any ideas?

Thanks!
Nikki

[fury]

What version of PHP, and how fast is the server running it? Also, are you running it locally or remotely? If you're running it remotely (on a server not running on your computer), then the speed of your connection could be what's causing the 8 seconds, because that looks like sufficiently efficient () code to me to not require 8 seconds of processing time, even for 200,000 rows.

[Princess Nikida]

What version of PHP, and how fast is the server running it? Also, are you running it locally or remotely? If you're running it remotely (on a server not running on your computer), then the speed of your connection could be what's causing the 8 seconds, because that looks like sufficiently efficient () code to me to not require 8 seconds of processing time, even for 200,000 rows.

Thanks for the quick reply!

I am running PHP 4.2.2 on a remote server. I am fairly sure that it is not my connection speed, as I thought of that and had the PHP output the microtime before and after the load. 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)...

Anyone else load that many rows at a time from a MySQL table?

[fury]

Wow. OK, well, do the ~200,000 rows change very much? If not, you could possibly cache the output once every so often into a template (like vBulletin does with its options list) and just do an eval on that template to parse everything. I did something like that that to my smilies array which was about 250something smilies big and was causing a bit of a slowdown (.5 seconds on the average thread), only I didn't cache it to a template, I cached it directly into the PHP code.

[Princess Nikida]

Wow. OK, well, do the ~200,000 rows change very much? If not, you could possibly cache the output once every so often into a template (like vBulletin does with its options list) and just do an eval on that template to parse everything. I did something like that that to my smilies array which was about 250something smilies big and was causing a bit of a slowdown (.5 seconds on the average thread), only I didn't cache it to a template, I cached it directly into the PHP code.

How does the caching work? I haven't checked out the caching of the options list in VB. Can you point me to a .php file to check and see?

I am not needing to display the records, but to use them. I already have the code ready to use them and everything works awesome (if I do say so myself ), but I am in the process of cutting the processing time down. During the optimization, I noticed that this section seemed to be the worst time-wise...

Thanks for the suggestions and checking my post out!

[fury]

When you go to change an option in the vBulletin options page (options.php), the current values are retrieved from the setting table. Then once you submit the changes, everything in that table is updated, as well as a string containing the list of all variables is compiled, in the form of ${varname} = "${value}" where {x} is the respective column name.

E.g. the variables named vbulletinoption1 and vbulletinoption2, respectively vBulletin Option 1 and vBulletin Option 2 from the control panel page, are both set to bleh, here is how the string looks

$var = "
$vbulletinoption1 = \"bleh\";
$vbulletinoption2 = \"bleh\";
"; and so on

This is done to every variable in the setting table through a while loop, I believe, and saved to a template (not a part of the HTML templates, just saved in the same table but with a non-used template set ID. This is also done to current birthdays and the logged in users record), and every time you load a page in vB, this template is called and the contents are put through eval(), therefore setting all the variables again. This is a much more efficient way than doing a while loop with every row of the setting table, as you may be aware, it is dozens of options (and thus rows) long However it is only efficient because the options are only updated when the admin updates them. Depending on how often the rows in your table will be updated and how often you will need to run this script, you may or may not be better off using a form of caching.

[Raz Meister]

Have you set spell_langid as an index? If not, that is your problem.

Also try executing "EXPLAIN SELECT hash, flags FROM spell_hash WHERE spell_langid = $lang_id" to get more info from mysql about the way it is being executed.