Increase speed by creating large array rather than querying DB?

[opalelement]

I will start off by saying that this is going to be very confusing as I don't find myself a good explainer... Try to bear with me though.

 

I am creating a PHP application that will find the connection between two items on our system. The DB tables are like this:

 

TABLE connections_enabled

COL item_id BIGINT(20)

 

TABLE connections

COL item_id BIGINT(20)

COL connected_to_id BIGINT(20)

 

So then I end up with values like the following:

 

connections_enabled

0001

0002

0003

0004

0005

 

connections

0001, 0008

0001, 0010

0002, 0004

0002, 0007

0005, 0007

0005, 0008

 

Then lets say I want to find out how 0001 and 0002 are connected... In my script I will apply the following logic:

 

1. Get connections of 0001 (returns 0008, 0010)

2. Get connections of each of the returned (0008 => 0001, 0005) (0010 => 0001) so now the 0010 path ends

3. Since 0008 still has 0005 unchecked, we get what 0005 is connected to (returns 0007 and 0008, but 0008 is already used and is therefore dropped)

4. We check what 0007 matches, and sure enough it is connected to 0002.

 

Final result: 0001 => 0008 => 0005 => 0007 => 0002

 

However, this will be on a much larger scale... each `connections`.`item_id` above only has 2 matches. In reality each one will have between 1 and 500 matches, 300,000 items with connections enabled, and they can go through up to 15 matches (compared to the 5 in the result above). Calling a mysql_query each time will take way too long.

 

My idea is that I have another script that runs every few hours to export all data to a cache folder as arrays in individual files. So the above `connections` table would be like this (not exactly, but similarly so):

 

main.php

$connarr = new Array();

require("$idNum.php");

 

0001.php

$connarr['0001'] = new Array(0008, 0010);

 

0002.php

$connarr['0002'] = new Array(0004, 0007);

 

0005.php

$connarr['0005'] = new Array(0007, 0008);

 

 

Then I can get the connected items by looping through $connarr[$whateverIdINeed].

 

I have no doubt that this would take quite a bit of fine-tuning. The question boils down to this: Would it be quicker/more efficient to compare array items require()d from separate files than it would be comparing through the database?

 

If you read all of that, thank you for hanging in there. If you understood it, all opinions would be appreciated. Feel free to ask if you have any questions.

 

Thanks,

Opalelement

[GKWelding]

what you will need to be thinking about is caching. I would suggest having a method of finding out when the tables were last updated, maybe an auto field in mysql with a timestamp. You will then query the data and store it, somehow, that's for you to decide what's best.

 

Next time you run the same query, check the timestamp, if no updates since query was last run then use the cached results.

 

If I were you i'd look at creating the array as you suggest, then serialize it, http://php.net/manual/en/function.serialize.php

 

If you need more help with anything then don't hesitate to ask as I'm a team leader on a project that is estimated to have over 1 million active users within the next 6 months and so I've had to become an expert on stuff like this, scalability, very quickly.