Thanks radoulov, I got your point now.
I decided on purpose to allow the table uploaded to contain a lot of redundant data.
Why?
Datas are meant to go through the table uploaded but not stay in it. Once a line is added to the table, it goes through various processes and is then deleted. It would take ages to explain it all but here are the basics:
proftpd adds a row each time a file is uploaded
some process work line by line like the one that extract informations from tgz because it implies working with the filesystem.
some processes work as updates of numerous lines at the same time like those that retrieve file_id, file_owner_id, file_mod_id, etc.
and there's more...
until the entry is deleted
So basically, there is a process that update lines one by one with as few operations as I can. Actually I just parse tar -tzvvf "$path".
Then the other processes can work much faster by adding unknown file_owner and file_mod and then updating the whole table at once with the corresponding ids.
This view might help you understand:
line 5 has just been added by proftpd, it has gone through no processes (status 0)
line 3 and 4 have gone through the process that reads info from tgz files (status 1)
line 1 and 2 have gone through the process that retrieves ids (status 2)
the next process add the record to the table version and delete it
To finish with your notes, I decided to separate table files and versions exactly for the reason you mentioned. If it was all in one table, the field file_path (255 char) would be repeated very often (because a lot of files exist in many versions).
The whole system is a backup facility. The table file records the address of each file with their latest mod/owner/group information (because one should not be able to retrieve an old version only because once in the past he had access to the file). The table versions records where is located each version of each files, no need to repeat file_path, file_mod and file_owner here.
Your remarks are a great help for me, don't hesitate to come back on what I said.
Well my idea is that if I retrieved ids one by one, I would run millions of queries:
Code:
for each record; do
UPDATE uploaded with tar -tzzf "$path" WHERE id = $id
INSERT INTO IGNORE mods (file_mod) VALUES (the current mod)
INSERT INTO IGNORE owner (file_owner) VALUES (the current owner)
INSERT INTO IGNORE files (file_path) VALUES (the current path)
UPDATE uploaded JOIN mods, owner, files SET mod_id =..., owner_id=... WHERE id = $id
done
This is no scripting language, it just a explanation of what it does.
This means that I will run 5 queries for each record.
Instead, I do that:
Code:
for each record; do
UPDATE uploaded with tar -tzzf "$path" WHERE id = $id WHERE id = $id
done
# once it's all done
INSERT INTO IGNORE mods (file_mod) SELECT DISTINCT mod FROM uploaded
INSERT INTO IGNORE owner (file_owner) SELECT DISTINCT owner FROM uploaded
INSERT INTO IGNORE files (file_path) SELECT DISTINCT file_path FROM uploaded
UPDATE uploaded JOIN mods, owner, files SET mod_id =..., owner_id=...
Which means I run 5 times less queries + 4.
Does it make sens?
Hello,
I am working on an application that uses allot of memory depending on the input. I am also working on more than one processing algorithm.
The program has a long processing time (hours) so it would be nice to be able to monitor the maximum memory footprint of the application during runs... (1 Reply)
Dear community,
I woul like to make a query to output all the users having the same IP address.
The table is somethig like:
name logged_ip
==== =========
user1 127.0.0.1
user2 127.0.0.2
user3 127.0.0.3
user4 127.0.0.1
user5 127.0.0.2
user6 127.0.0.5I used this query... (4 Replies)
I want records which have more than one and different value in the second column on the below sample file.
Ex, I have the samle file below :-
XYZ 1
XYZ 3
abc 1
abc 1
qwe 2
qwe 1
qwe 3
I want to select XYZ and QWE line only. (6 Replies)
I feel stupid for asking this because it seems that MYSQL code isn't working the way that I think it should work.
Basically I wrote code like this:
select * from `Test_DC_Trailer` HAVING max(DR_RefKey);
Where the DR_RefKey is a unique numeric field that is auto iterated (like a primary key)... (7 Replies)
I have a file with 3 columns
I want to get the record with the max salary. If there are more than 1 record having the highest salary, i want any one of them.
empid,ename,Sal
1,abc,100
2,def,200
3,xyz,300
4,pqr,100
5,mnq,300
Output
Any record with 300 as salary
Please use next... (1 Reply)
Hi, I would just like to know how to get the file with the max filename on a directory and get rid of all the others. For example, in directory A:/ i have the ff files:
APPLE2001
APPLE2002
APPLE2003
GRAPE2004
what I want to get is the max in files whose filenames start with APPLE*,... (4 Replies)
hi..
i need to optimize my select query ..
my situation is like this ..
i have 15 lac recors in my table..
the following query takes nine seconds to give the required output..
SELECT max(ah.AUC_AMT),
SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (1 Reply)
hi..
i need to optimize my select query ..
my situation is like this ..
i have 15 lac recors in my table..
the following query takes nine seconds to give the required output..
SELECT max(ah.AUC_AMT),
SUBSTRING_INDEX(GROUP_CONCAT(SUBSTRING_INDEX(ah.AUC_CUS_NAME,'@',1) order by AUC_AMT... (0 Replies)
Hi,
I have a file with multiple records...and I have to select records based on first column....here is the sample file...
I01,abc,125,1a2,LBVI02
I01,abc,126,2b5,LBVI02
I02,20070530,254,abc,LLBI01
I02,20070820,111,bvd,NGBI01
I need all records with I01 in first field in one file and... (8 Replies)
06-29-2010
