Right, I have a large database with a lot of repeating data. The repeating data will be sorted but the database will still be extremely large - at the moment it's about 1.8m rows, but I guess that isn't that large.
one of the fields is called 'key' and is a repeating field that coudl be one of say.. 50,000 things.
'company' is a field that states the name of a company.
I run the following sql query:
SELECT distinct key from adata where company like "%MBNA%"
It then runs through the database of 1.8m entries and finds how many individual keys that the company MBNA is uniquely associated with.
The problem is this is for a website back end, where the response has to be quick. Currently this takes about 3 minutes to complete the query.. I assume it has to scan the entire 1.8m row database. Is there anyway to speed this up? The MySQL database does absolutely TONS of hard disk read/writes during the query. Ugh it's so annoying, I need to to be so much faster. Maybe if the whole database is in ram?
The database is a dedicated windows XP machine w/1 gb ram and a 2.8ghz processor. Would it be significantly faster if I installed CentOS?
Please help
