Re: Improving a MySQL Search

Create some indexes. I spend most of my day messing around with a table of 2000000 records, and indexes increased the speed from 40 seconds to 55 minutes for a query, down to the longest being less than 5 seconds. On 28 Feb 2005, at 16:42, James Nunnerley wrote:

I'm creating a serious of pages that show various aspects from a Syslog output. The main information is currently stored in one MySQL table - which after having been run for about 2 and a bit months has got nearly 1000000 records. With this amount of information, it's taking a large time to carryout even the simplest query. Someone has suggested I look at setting up an archive, for slower searches, and a more recent table for quicker searching, but I'm reluctant to do this, as it would take quite a bit of time to copy all the information across as it stands, and then also to run a cron which transfers between the live and archive. Can anyone suggest some quick easy methods for speeding up the search? Cheers James