homepage Welcome to WebmasterWorld Guest from 54.196.159.11
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
MySQL Index Design
XMLMania




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

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?

 

XMLMania




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

Hmm forget it...

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

:-(

charlier




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

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.

XMLMania




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

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

Cheers

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved