On Fri, Oct 24, 2008 at 6:59 AM, Mark Goodge <[EMAIL PROTECTED]> wrote: > I'd appreciate some advice on how best to handle a biggish dataset > consisting of around 5 million lines. At the moment, I have a single table > consisting of four fields and one primary key: > > partcode varchar(20) > region varchar(10) > location varchar(50) > qty int(11) > PRIMARY KEY (partcode, region, location) > > The biggest variable is partcode, with around 80,000 distinct values. For > statistical purposes, I need to be able to select a sum(qty) based on the > other three fields (eg, "select sum(qty) from mytable where partcode ='x' > and region = 'y' and location = 'z'") as well as generating a list of > partcodes and total quantities in each region and location (eg, "select > sum(qty), partcode from mytable where region = 'y' and location = 'z' group > by partcode"). > > The selection is done via a web-based interface. Unfortunately, it's too > slow. So I want to be able to optimise it for faster access. Speed of > updating is less crucial, as it isn't updated in real-time - the table gets > updated by a nightly batch job that runs outside normal working hours (and, > apart from the rare occasion when a location is added or removed, the only > thing that changes is the value in qty). > > Does anyone have any suggestions? My initial thought is to replace the > region and location varchar fields with int fields keyed to a separate list > of region and location names. Would that help, or is there a better way? > > Mark > -- > http://mark.goodge.co.uk - my pointless blog > http://www.good-stuff.co.uk - my less pointless stuff > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > >
Your first query uses an index, so that should be fairly quick. Your second query does not because your only index starts with partcode, but you are not searching on partcode. Add an index on region+location. That should speed the second query up considerably. If you want to normalize your data, you should replace region and location with "ids" that link to another table. While that will speed up searches, the speed improvement likely won't be noticeable for the searches you listed. Make sure query cache is enabled. That will help a lot since the result of the search will be cached until the table changes. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
