Welcome to WebmasterWorld Guest from 188.8.131.52
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?
For instance for (title='$title' AND body='$body') you could create a checksum $cstb = md5("$title$body") and use that for the first test ie cstb=$cstb, you could do the same for the other pairs and that would cut your select down to 4 short string comparisons. BTW mysql will only use one index per query so sometimes it is better to do several queries with a simple where clause and use a bit of script code to merge the results.