Welcome to WebmasterWorld Guest from 54.161.255.61

Forum Moderators: coopster & jatar k

MySQL Index Design

   
5:11 pm on Oct 27, 2004 (gmt 0)

10+ Year Member



Hi,

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')

Table:

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`)
) TYPE=MyISAM;

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?

7:25 pm on Oct 27, 2004 (gmt 0)

10+ Year Member



Hmm forget it...

I've gone over some things an it appears I would need an index on each column combination. SCREW THAT!

:-(

7:52 am on Oct 28, 2004 (gmt 0)

10+ Year Member



Maybe you could create a couple new fields with 'ContentID's. Without knowing what your actual data is like its hard to suggest anything that will work for sure. But the idea would be to create checksums of the current fields or pairs of current fields and then select on the checksums.

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.

10:04 am on Oct 28, 2004 (gmt 0)

10+ Year Member



Thats a cool idea, I'll definetly look into that method.

Cheers

 

Featured Threads

Hot Threads This Week

Hot Threads This Month