[UPHPU] OT: MySQL using repeating keys under load

[Tyler Gee]

On 11/30/06, Mike Potter <mike at mikenbob.com> wrote:
> >   I would like to use PHP and MySQL to write and retrieve records from this table up to Col A having a value of 9999 then start back at 1 and repeat.
>
>
> I may be completely off base here, so I am sorry. In fact, please feel free to
> flame me if it doesn't work. This would have to be tested to see if two updates
> would step on each other, but it would get rid of the id file you have to open
> and update. (watch the linewraps in the code)
>
> I took into account you have mysql 4, and since you don't want to delete
> columns, you first fill your table with default data...
>
> col_a is the first 1-9999 id [int(4),unsigned,primary_key]
> col_b is the custom value [int(13),unsigned]
> col_c is the value we use for microtime (float,unsigned,indexed)
>
> <code>
>
> // prefill table data - run only once
> $sql_default = 'insert into `table_name` values ';
> for($x=1;$x<10000;$x++){
>         $sql_default .= '(' . $x . ',0,' . $x . '),';
> }
> $sql_default = rtrim($sql_default,',');
>
> </code>
>
>
> then, let the program do the add/replace by itself in one mysql call.
>
> <code>
>
> list($usec, $sec) = explode(" ", microtime());
> $microtime = ((float)$usec + (float)$sec);
> $sql = 'replace `table_name` select `col_a`,' . $some_data . ',' . $microtime  .
> ' from `table_name` order by `col_c`,`col_a` limit 1';
>
> </code>
>
> Now, you didn't say anything about returning the id, so if you need to do that,
> just ...
>
> <code>
>
> $sql_last_id = 'select `col_a` from `table_name` where `col_b` = ' . $some_data
> . ' and `col_c` = ' . $microtime;
>
> </code>
>
> What are your thoughts???

I thought of this too but then it occurred to me they are different
people requesting the same page that is causing the entries into the
table, not one page being called once.  So it can't be done in a loop.

> --Mike
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>


-- 
~Tyler