|
|
Posted by Norman Peelman on 12/05/07 22:03
Mikhail Kovalev wrote:
> On 5 Des, 20:46, Norman Peelman <npeel...@cfl.rr.com> wrote:
>> Mikhail Kovalev wrote:
>>> On 5 Des, 18:42, Norman Peelman <npeel...@cfl.rr.com> wrote:
>> <snipped>
>>
>>
>>
>>>> INSERT INTO nodes (node_address, node_count) VALUES ($node_address,
>>>> $node_count) ON DUPLICATE KEY UPDATE node_count=node_count+1
>>>> ...will INSERT new entries and UPDATE existing entries in one swoop.
>>>> Norm
>>> Ok, suppose I'm joining to structures which have been created
>>> separately,
>>> from before i have ('112/225/930', 3)
>>> and i want to add ('112/225/930', 2), which also happens to be present
>>> in the second table, only with a different count,
>>> to make ('112/225/930', 5)
>>> From there I want to make it the general case so that when updating an
>>> entry with count 1 I am actually adding ('112/225/930', 1) to the
>>> existing one, if it exists:
>>> INSERT INTO nodes (node_address, node_count) VALUES (<new_address>,
>>> <new_count>) ON DUPLICATE KEY UPDATE node_count = node_count +
>>> <new_count>
>>> Is this correct? (Do I have to use <> in VALUES, I'm following an
>>> example which does it?)
>> No replace everything '<..>' with your own variable name, that will do it.
>>
>> $new_count = 5
>> ...ON DUPLICATE KEY UPDATE node_count = $new_count
>>
>> Not sure what you mean by 'second table'. How many do you have? Though
>> this was one table...
>>
>> Norm
>
> I have different databases for different types of sequences. Each
> sequence is some tousands elements in length and is recorded by chunks
> of small series each from 1 to 20 elements long. Sometimes I join
> different databases together and analyze each one separately and then
> in relation to the joint database.
>
> I have been thinking, if a sequence is for instance 5.000 elements
> long, chunks 1-20, and there are no duplicates (absolutely unlikely,
> but still) I will end up having a database with around 105.000
> entries...
> My current solution is probably better because it saves so much
> space(?)
>
> An entry like this in the current solution:
> 112 (310)
> 225 (20)
> 930 (6)
> 700 (1)
> 7 (1)
> 812 (1)
>
> Equals this in the new system I am about to implement:
>
> ('112', 310)
> ('112/225', 20)
> ('112/225/930', 6)
> ('112/225/930/700', 1)
> ('112/225/930/700/7', 1)
> ('112/225/930/812', 1)
>
> I don't know how a flat MySQL database of 100.000-200.000 entries like
> these will perform. I'm now also considering a filesystem as the
> database, as C has suggested.
I would imagine it would perform quite well as each one of the
addresses is a key (index) into the database. MySQL won't have to
examine all the entries that start with 112 to find where 930 or 700 or
whatever is. It knows exactly where 112/225/930/812/???/???/??? is. The
INSERT UPDATE will take less than a second on average most likely.
All this being said, you can do the same thing with your current array
in memory style using the same syntax:
$your_array['112/225/930'] = 6
$your_array['112/225/930/700'] = 1
the only difference is you still have to load it into memory (an array)
to use it. If you go with MySQL and still need an array in memory at
some point, a simple:
$dataset = array();
$result = mysql_query("SELECT * FROM nodes");
while($row = mysql_fetch_assoc($result))
{
$dataset[$row['node_address']] = $row['node_count'];
}
will get you your entire dataset into an in memory array in which you
would access just like sql:
$dataset['112'] = 310
$dataset['112/225'] = 20
$dataset['112/225/930'] = 6
$dataset['112/225/930/700'] = 1
$dataset['112/225/930/700/7'] = 1
Norm
Navigation:
[Reply to this message]
|