I have a query:
SELECT COUNT(id) AS count FROM $index WHERE (title='$title' AND body='$body') OR (feedurl='$row[feedurl]' AND itemurl='$link') OR (title='$title' AND itemurl='$link') OR (body='$body' AND itemurl='$link')
CREATE TABLE `items` (
`id` bigint(50) unsigned NOT NULL auto_increment,
`title` text NOT NULL,
`feedtitle` varchar(150) NOT NULL default '',
`body` text NOT NULL,
`feedurl` text NOT NULL,
`itemurl` text NOT NULL,
`lang` char(2) NOT NULL default 'en',
`time` int(10) NOT NULL default '0',
PRIMARY KEY (`id`)
Currently the SELECT is being processed on 300K rows, with no index on the columns. The SELECT takes roughly 2 seconds to complete, which is to long for my liking.
I have tried altering the table with an index, unfortunately the index was useless as it didn't cover all columns, AND the servers just died (ouch).
This is alot of data i'm working with circa 1 GB :-( so I need someone to try and confirm my ALTER statement will help speed up the query above^.
Here is the alteration I predict will do what I want:
ALTER TABLE items ADD INDEX(title(50), body(100), feedurl(50), itemurl(50))
Is it any good?