Thanks to you and all the others who helped me out with this. I *am* using
MyISAM tables, and everything is working the way I want it to. This is every
bit as fast as the last_insert_id, with the added benefit that it is
accurate no matter which thread I hit 8-). Yes, I am using pconnects, and my
apache has spawned about 7 children, so there's that angle working against
last_insert as well.
I'm so impressed with the speed of MySQL. And my memory problems seem to
have evaporated since I tightened up my SELECT statements and got rid of the
table.* requests. Unbelievable performance on a PowerMac 9500 (180 MHz
604e).
Walter
On 7/20/01 9:24 PM, "Joshua J. Kugler" <[EMAIL PROTECTED]> wrote:
> Yes, you are correct in assuming that, but ONLY if you use MyISAM tables.
> BDB and ISAM tables reuse number, kind of.
>
> Ex:
>
> 1
> 2
> 3
> 4
> 5
>
> If you delete 5, the next record you insert will have an ID of 5. If you
> delete 4 instead of 5, the next one inserted will have an ID of 6. So, BDB
> and ISAM tables do kind of a MAX(id)+1, whereas MyISAM tables actually keep
> track of the last used auto increment number.
>
> Hope that helps!
>
> j----- k-----
>
> On Friday 20 July 2001 17:11, you wrote:
>> That I'm trying to do here is get the VALUE of hit_id from the last row of
>> hits. It is an auto-increment number, but it is much higher than the count
>> of the rows, because people go through and delete their hits from time to
>> time. Am I correct in assuming that auto-numbers are never re-used?
>>
>> Walter
>>
>> On 7/20/01 7:06 PM, "Joshua J. Kugler" <[EMAIL PROTECTED]> wrote:
>>> Read the manual again on last_insert_id(). That function only gives you
>>> the unique id of the last insert *for that thread* If you connect, don't
>>> do any inserts, and select last_insert_id, there is no guarentee what you
>>> will get. If you are trying to find the number of rows, it would be much
>>> easier to do this:
>>>
>>> SELECT COUNT(*) AS pixels FROM hits
>>>
>>> Try that. From the looks of what you are doing, that will be accurate
>>> every time. COUNT(*) knows how many rows there are in a table.
>>> last_insert_id() has nothing to do with how many rows are in a table.
>>>
>>> If you are going to delete old rows one day, then you might want to do
>>> this:
>>>
>>> SELECT MAX(id) as pixels FROM hits
>>>
>>> Hope that helps.
>>>
>>> On Friday 20 July 2001 13:31, Walter Lee Davis wrote:
>>>> Currently, I have been counting the rows in PHP in order to get the
>>>> value of the last hit_id in the database like this:
>>>>
>>>> $sql4 = "SELECT hits.hit_id from hits";
>>>>
>>>> $result = mysql_query ($sql4, $connection)
>>>> or die("error #" . mysql_errno() . ": " . mysql_error());
>>>> $last = mysql_num_rows($result) - 1;
>>>> $go = mysql_data_seek($result, $last);
>>>> $row = mysql_fetch_object($result);
>>>> $pixels = number_format($row->hit_id);
>>>>
>>>> Which is really expensive on my very tall hits table. I hit upon doing
>>>> it in MySQL, which is much, much faster:
>>>>
>>>> $sql4 = "SELECT hits.hit_id
>>>> as pixels
>>>> from hits
>>>> WHERE hit_id=LAST_INSERT_ID();";
>>>>
>>>> $result = mysql_query ($sql4, $connection)
>>>> or die("error #" . mysql_errno() . ": " . mysql_error());
>>>> $row = mysql_fetch_object($result);
>>>> $pixels = number_format($row->pixels);
>>>>
>>>> It's staggering the difference in speed. But if I sit there and hit
>>>> refresh on the browser, I get wildly different values for $pixels. It
>>>> jumps around the actual number by +-10 or 12. The first method is
>>>> precisely the same every time (unless a hit is recorded in the interim).
>>>> Am I doing something wrong here? Can I not count on MySQL to know how
>>>> many rows it has recorded in a table? Any idea why that select statement
>>>> would select a different row each time?
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
