On Sun, Mar 06, 2011 at 11:03:23AM +0300, Allan Kamau wrote: > I would assume the primary key or unique indexes are the cornerstone > of each insertion and deletion. <snip> > Or am I missing a crucial point. The real issue is that you can have four programs all doing count(*) and all getting different answers. How? Because what you see is dependant on what snapshot of the database you're looking at. And information about what snapshot can see what tuple is stored in the table. An index does not have enough information to work this out. The DBs that don't have this issue are usually like MyISAM, no transactions so no issues about different snapshots. And crappy concurrency. As soon as you go to more advanced systems the easy option falls away. For example http://www.mysqlperformanceblog.com/2006/12/01/count-for-innodb-tables/ If it's really really important there are ways you can use trigger tables and summary views to achieve the results you want. Except it's expensive and when people are told that all of the sudden the count(*) performance isn't so important any more. :) The other option is visibility data in the index. Doubles the size of your indexes though. Have a nice day, -- Martijn van Oosterhout <kleptog@xxxxxxxxx> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle
Attachment:
signature.asc
Description: Digital signature
