Re: [PHP] High ID (unique, auto increment) causes slow responses on PHP (MySQL)
On Sat, Jun 13, 2009 at 3:34 PM, Stuart wrote: > 2009/6/13 דניאל דנון : >> I've built a certain system where every time new user enters it creates a >> "guest row" on "guests" table. he also gets an identifying cookie. >> >> The table contains several fields, one of them is ID which is auto increment >> and unique >> >> When he does a certain action it gets recorded in the "done_action" table >> with several fields on it, one of them is "guest_id" and his ID on it. >> >> >> Problem is the guests table has about 30,000 rows (which means ID is high). >> >> I was wondering whether I should somehow change how the system works. >> >> I've thought about couple of options >> >> A weekly "cleanup" job. opens a table called "guests_tmp", *copies* all data >> from guests to guests_tmp. >> >> every row it copies it also changes the ID on the table done_action. >> >> What do you think? > > I think your problem has nothing to do with high IDs. I have tables in > MySQL with primary key IDs that have recently passed 2 billion and > they're still as responsive as they were with 1 row!! > > What specific operations are slow, and are you sure it's the MySQL > query that's being slow and not something in the surrounding code? If > it is the MySQL query what is it and what engine are your tables > using? > > -Stuart > > -- > http://stut.net/ > Right. 30k rows in most databases is nothing. Neither the number of rows nor the size of the number should cause problems at that size. Is there a chance that the table is incorrectly indexed? Every index has to be updated when a new row is added, so a table with a lot of unnecessary indexes (for example, every column has its own index) can get dreadfully slow as it fills up. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] High ID (unique, auto increment) causes slow responses on PHP (MySQL)
2009/6/13 דניאל דנון : > I've built a certain system where every time new user enters it creates a > "guest row" on "guests" table. he also gets an identifying cookie. > > The table contains several fields, one of them is ID which is auto increment > and unique > > When he does a certain action it gets recorded in the "done_action" table > with several fields on it, one of them is "guest_id" and his ID on it. > > > Problem is the guests table has about 30,000 rows (which means ID is high). > > I was wondering whether I should somehow change how the system works. > > I've thought about couple of options > > A weekly "cleanup" job. opens a table called "guests_tmp", *copies* all data > from guests to guests_tmp. > > every row it copies it also changes the ID on the table done_action. > > What do you think? I think your problem has nothing to do with high IDs. I have tables in MySQL with primary key IDs that have recently passed 2 billion and they're still as responsive as they were with 1 row!! What specific operations are slow, and are you sure it's the MySQL query that's being slow and not something in the surrounding code? If it is the MySQL query what is it and what engine are your tables using? -Stuart -- http://stut.net/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] High ID (unique, auto increment) causes slow responses on PHP (MySQL)
Just use SERIAL id and you will be good ;) (that's an alias for something like BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) It shouldn't bother you as long as it works. On Sat, Jun 13, 2009 at 9:16 PM, דניאל דנון wrote: > I've built a certain system where every time new user enters it creates a > "guest row" on "guests" table. he also gets an identifying cookie. > > The table contains several fields, one of them is ID which is auto > increment > and unique > > When he does a certain action it gets recorded in the "done_action" table > with several fields on it, one of them is "guest_id" and his ID on it. > > > Problem is the guests table has about 30,000 rows (which means ID is high). > > I was wondering whether I should somehow change how the system works. > > I've thought about couple of options > > A weekly "cleanup" job. opens a table called "guests_tmp", *copies* all > data > from guests to guests_tmp. > > every row it copies it also changes the ID on the table done_action. > > What do you think? > > > -- > Use ROT26 for best security >
[PHP] High ID (unique, auto increment) causes slow responses on PHP (MySQL)
I've built a certain system where every time new user enters it creates a "guest row" on "guests" table. he also gets an identifying cookie. The table contains several fields, one of them is ID which is auto increment and unique When he does a certain action it gets recorded in the "done_action" table with several fields on it, one of them is "guest_id" and his ID on it. Problem is the guests table has about 30,000 rows (which means ID is high). I was wondering whether I should somehow change how the system works. I've thought about couple of options A weekly "cleanup" job. opens a table called "guests_tmp", *copies* all data from guests to guests_tmp. every row it copies it also changes the ID on the table done_action. What do you think? -- Use ROT26 for best security
