Re: [PHP-DB] MySQL circular buffer

[OKi98]

 (i.e. stack_id  500  stack_id  601 vs where stack_id = 500 limit 100)
stack_id between 501 and 600 (stack_id  500  stack_id  601) is much 
better




What I would like to know is if anybody has experience
implementing this sort of data structure in MySQL (linked list?) or
any advice.
  

tables:
process_table:
   IDProcess PK

mail_table
   IDMail PK
   IDPrrocess FK references process_table.IDProcess ON DELETE SET NULL
   Mail TEXT
   From VCH(255)
   TO VCH(255)
   DateModified DATE ON UPDATE CURRENT_TIMESTAMP
   Mailed TINYINT DEFAULT 0;
--
code:
define('MaxProccessTimeMinutes', 30);
define('BatchCount', 100);

INSERT INTO process_table VALUES ();
$lnIdProcess = GetLastIDProcess();

label send_mail:
$ldDateExpired = time() - MaxProccessTimeMinutes * 60;
UPDATE mail_table
   SET IDProcess = $lnIdProcess
   WHERE
  Mailed = 0 AND
  (
   IDProcess IS NULL OR
  (
   IDProcess IS NOT NULL AND
   DateModified = $ldDateExpired
  );
  )
   LIMIT BatchCount;
while ($result = SELECT IDMail, MailText, From, To FROM mail_table WHERE 
IDProcess = $lnIDProcess)

{
   if (send_mail())
   {
   UPDATE mail_table SET Mailed = 1 WHERE IDMail = $result['IDMail'];
   }
}
if there are other mails goto send_mail;
else DELETE FROM process_table WHERE IDProcess = $lnIDProcess;

this way more than one process can send mails, also if one process exits 
prematurely the other can send his emails later.

Time to time run cron:
   DELETE FROM mail_table WHERE Mailed = 1;
   rebuild indexes on mail_table;

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] One field or a new table?

[Evert Lammerts]

mysql.com says:

Foreign key enforcement offers several benefits to database developers:
*  Assuming proper design of the relationships, foreign key
constraints make it more difficult for a programmer to introduce an
inconsistency into the database.
*  Centralized checking of constraints by the database server
makes it unnecessary to perform these checks on the application side.
This eliminates the possibility that different applications may not
all check the constraints in the same way.
*  Using cascading updates and deletes can simplify the
application code.
*  Properly designed foreign key rules aid in documenting
relationships between tables.

Do keep in mind that these benefits come at the cost of additional
overhead for the database server to perform the necessary checks.
Additional checking by the server affects performance, which for some
applications may be sufficiently undesirable as to be avoided if
possible. (Some major commercial applications have coded the foreign
key logic at the application level for this reason.)

(http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html)

Obviously. Regardless, whenever a many to many relationship exists, a
link table is the solution, with or without foreign keys. I don't know
what the overhead of using foreign keys is, but that's something you
can test. ON UPDATE and ON DELETE do make things a lot easier.

Evert


On Fri, Jun 20, 2008 at 2:41 PM, J. Hill [EMAIL PROTECTED] wrote:
 In a number of cases I have used link tables like you are suggesting here,
 but I am curious if using foreign key constraints, in the situation
 described, is the best solution?

 I am not an expert (so this is a real question), but I remember some years
 ago a database programmer (I believe he worked for MySQL) advising against
 the use of foreign key constraints when possible, due to the overhead when
 updating. I know avoiding them requires careful programming/scripting, but
 his argument seems logical to me.

 If he was wrong, I'd sure like to know, because that would make my
 development work much easier.

 Jeff.


 Evert Lammerts wrote:

 Whenever you can say:

 X has zero or more Y and Y has zero or more X you're talking about
 a Many to Many relationship between X and Y, and you need a link
 table with foreign key constraints.

 Example:

 Table X:
 id name
 1   A
 2   B
 3   C

 Table Y:
 id name
 1   K
 2   L
 3   M

 Linktable:
 X_id Y_id
 1  1
 1  2
 2  3
 2  1
 3  1
 3  2
 3  3

 The link table is the only table that needs to be updated when a
 relationship between an entry of X and an entry of Y starts to exist
 (in your example, a user from X collects an item from Y).

 It is wise to use foreign keys when using linktables - but you can
 only do this when using INNODB. Check
 http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

 Evert

 On Fri, Jun 20, 2008 at 8:38 AM, Hussein Jafferjee
 [EMAIL PROTECTED] wrote:


 Hey Guys,



 I have been developing for a while and never found the best solution yet.
 The question is lets say a game has 10 collectibles you can earn, to keep
 track of the number of collectibles each user has, do you have one field in
 the users table with all the numbers separated via a divider, or do you make
 a new table called collectibles and have each one as a field?



 These are high traffic sites (100,000+ people) and so I was initially
 thinking the solution of creating a separate table is best because the main
 users row is loaded on every page, and on top of that you would need to use
 explode on the field.



 Currently I am having a separate table, but I was wondering if people have
 better solutions.



 Hussein J.







-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php