Re: High ID (unique, auto increment) causes slow responses on PHP (MySQL)

On Sat, Jun 13, 2009 at 3:34 PM, Stuart<stuttle@gmail.com> wrote: > 2009/6/13 דניאל דנון <danondaniel@gmail.com>: >>  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