tag:blogger.com,1999:blog-32189452.post2649144713013212677..comments2020-10-01T16:11:58.925+01:00Comments on ScotGrid: Gone with the Indices: A story of optimisation and DPM, set against the thrilling backdrop of MySQL.Graeme Stewarthttp://www.blogger.com/profile/04113191724360870254noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-32189452.post-21044901071221897842009-03-02T11:33:00.000+00:002009-03-02T11:33:00.000+00:00It's worth pointing out that the index to deal wit...It's worth pointing out that the index to deal with: <BR/><BR/> select MAX(lifetime) from dpm_get_filereq where pfn = 'some pfn here'<BR/><BR/>is actually slightly different from the others. The others turn large scans into small scans over at most a few hundred rows, rather than over the whole table.<BR/><BR/>The index created: <BR/><BR/> create index pfn_lifetime on dpm_get_filereq (pfn(255), lifetime);<BR/><BR/>replaces an existing index on pfn, so it was already a small scan. This is because the query is so common, it's worth further optimistation. The second index turns the small scan into a direct lookup - no scan at all.<BR/><BR/>The hope was that if the indices are kept in RAM, then we remove disk access totally.Stuart Purdiehttps://www.blogger.com/profile/08473287949581285669noreply@blogger.com