[SOLVED] COUNT + INDEXES

satanik · September 2, 2008

Hi there guys.

Im developing a community site. The site itself is almost finished. Today I decided to make a stress test to my database and I get several queries corrected and indexes added.

Anyway, I'm stuck on a query. The query takes around 22 secs to complete.

TABLE members_blogs has about 20 000 rows

TABLE members_blogs_comments has about 70 000 rows

The query:

SELECT b.title,b.id,c.comments

FROM zym4921_members_blogs AS b

LEFT JOIN (

SELECT COUNT(*) AS comments,blogid

FROM zym4921_members_blogs_comments

GROUP BY blogid

) AS c ON c.blogid=b.id

ORDER BY b.date DESC LIMIT 6

The explain:

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY b ALL NULL NULL NULL NULL 19131 Using temporary; Using filesort

1 PRIMARY <derived2> ALL NULL NULL NULL NULL 9985

2 DERIVED zym4921_members_blogs_comments index NULL blogid 4 NULL 64299 Using index

The PRIMARY query takes like 0.0006 secs to run which is:

SELECT b.title, b.id

FROM zym4921_members_blogs AS b

ORDER BY date DESC

LIMIT 0 , 30

The sub-query takes 0.001 sec to run which is:

SELECT COUNT( * ) AS comments, blogid

FROM zym4921_members_blogs_comments

GROUP BY blogid

So, for what reason the left join of this two queries takes 22sec?

Anyway, I build to queries that produce the same results which are:

FIRST which takes like 0.2 seconds to run

SELECT b.title,b.id,COUNT(c.id) AS comments FROM zym4921_members_blogs AS b LEFT JOIN zym4921_members_blogs_comments c ON c.blogid=b.id GROUP BY b.id,c.blogid ORDER BY b.date DESC LIMIT 6

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE b ALL NULL NULL NULL NULL 20407 Using temporary; Using filesort

1 SIMPLE c ref blogid blogid 4 plasmo.b.id 3 Using index

SECOND which takes 0.0 seconds to run

SELECT b.title,b.id,(SELECT COUNT(*) FROM zym4921_members_blogs_comments WHERE blogid=b.id) AS comments FROM zym4921_members_blogs AS b ORDER BY b.date DESC LIMIT 6

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY b index NULL date 4 NULL 20407

2 DEPENDENT SUBQUERY zym4921_members_blogs_comments ref blogid blogid 4 plasmo.b.id 3 Using index

The last query is the best one because the primary query type is index; second query type is ALL, which performs full-scan and not index-scan). Not to mention that the first ones has "Using temporary, Using filesorting"

Any other suggestions? Why is the old query taking 22secs? Should I use the second alternative query I made?

fenway · September 3, 2008

When you make a derived table, that join has no index to use... but like you did in the "FIRST" query, the correct way is with a LEFT JOIN directly, not via a derived table. The "SECOND" query uses a dependent subquery, which is worse than "FIRST" -- but it looks like you've added an index on date, which makes it "look" better. Re-run the "FIRST" query -- it's the best way to go.

satanik · September 3, 2008

Yes I do have a index on date, but if I remove the date index it has no significant impact on the "FIRST" or "SECOND"

The "SECOND" query seems to be faster in all cases. So why do you say that it's worse if i got better results with them?

Any way to improve the "FIRST" query to get even better results than the "SECOND" query?

thanks

fenway · September 3, 2008

Sorry, I didn't see that group by for "FIRST" -- why two columns there?

satanik · September 3, 2008

Sorry, I didn't see that group by for "FIRST" -- why two columns there?

sorry it was a typo, though even removing b.id in the GROUP, staying only c.blogid the query takes 0.2 secs to run, and second one takes 0.0

fenway · September 3, 2008

Sorry, I didn't see that group by for "FIRST" -- why two columns there?

sorry it was a typo, though even removing b.id in the GROUP, staying only c.blogid the query takes 0.2 secs to run, and second one takes 0.0

I'd like to see that EXPLAIN... I find it hard to believe that it's still won't pick up "date" as a possible index row for use to prevent the filesort.

However, because you're grouping from one table and ordering by another, your subquery version may in fact be better in this particular case.

satanik · September 4, 2008

+----+-------------+-------+------+---------------+--------+---------+-------------+-------+---------------------------------+

+----+-------------+-------+------+---------------+--------+---------+-------------+-------+---------------------------------+

| 1 | SIMPLE | c | ref | blogid | blogid | 4 | plasmo.b.id | 1 | Using index |

+----+-------------+-------+------+---------------+--------+---------+-------------+-------+---------------------------------+

2 rows in set (0.00 sec)

I dunno why he is not picking up date but again the mysql optimizer has limitations with order by clauses.

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

fenway · September 4, 2008

That's very strange.... but at least date should be listed in possible keys?!?

satanik · September 4, 2008

yep it should and i dunno why... ???

anyway, im placing count fields (no_comments,no_visits) etc for each blog so i dont need to COUNT each time the user refreshes the page, because this query is executed in every refresh (its displayed in a quick latest blogs box).

do you think its a good approach to use count fields?

fenway · September 4, 2008

do you think its a good approach to use count fields?

Definitely for InnoDB... but even for MyISAM tables, it really depends how often this query will be removed from the query cache.

satanik · September 4, 2008

ty very much just another quick question

SELECT g.game,g.image,n.title,n.id,n.no_comments

FROM news AS n,games AS g

WHERE n.gameid=g.id AND n.type="1" AND n.status="1"

ORDER BY n.date

DESC LIMIT 6

+----+-------------+-------+------+--------------------+-------------+---------+-------------+------+----------------------------------------------+

+----+-------------+-------+------+--------------------+-------------+---------+-------------+------+----------------------------------------------+

| 1 | SIMPLE | n | ref | gameid,type_status | type_status | 2 | const,const | 1633 | Using where; Using temporary; Using filesort |

+----+-------------+-------+------+--------------------+-------------+---------+-------------+------+----------------------------------------------+

I made an index on the table News (n) with 2 columns: type and status.

I have tried a lot of alternative index to also make the optimizer to use date to avoid using filesort, including adding date to type_status index with no success.

Is there any way to get rid of filesort?

Anyway i think its not so bad, because as soon as the first row is found the filesorting ends (which is almost instantly).

fenway · September 4, 2008

Depends on your cardinality of type & status... if they are poor, it shouldn't really use them, but if there's just one, then perhaps it's better. You can also try adding date as the *first* column in the index....

satanik · September 5, 2008

modifed the index to (`date`,`type`,`status`) and here is the explain

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+

+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+

its refusing even to use the index now.

any other solution?

fenway · September 5, 2008

Could you post the table structure (CREATE TABLE syntax) and some sample data? I'll see what I can come up with... which version, btw?

satanik · September 5, 2008

CREATE TABLE `zym4921_news` (

`id` int(10) unsigned NOT NULL auto_increment,

`userid` int(10) unsigned NOT NULL,

`gameid` int(10) unsigned NOT NULL,

`countryid` int(10) unsigned NOT NULL,

`title` varchar(50) NOT NULL,

`intro` varchar(1000) NOT NULL,

`more` text NOT NULL,

`banner` varchar(200) default NULL,

`date` int(10) unsigned NOT NULL,

`last_edit_date` int(10) unsigned default NULL,

`last_edit_by` int(10) unsigned default NULL,

`status` enum('1','2','3') NOT NULL,

`type` enum('1','2') NOT NULL,

`no_comments` int(10) unsigned NOT NULL default '0',

`no_visits` int(10) unsigned NOT NULL default '0',

`no_votes` int(10) unsigned NOT NULL default '0',

`rate` double unsigned default NULL,

PRIMARY KEY (`id`),

KEY `userid` (`userid`),

KEY `last_edit_by` (`last_edit_by`),

KEY `countryid` (`countryid`),

KEY `gameid` (`gameid`),

KEY `type_status` (`type`,`status`),

CONSTRAINT `zym4921_news_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `zym4921_members` (`id`),

CONSTRAINT `zym4921_news_ibfk_2` FOREIGN KEY (`last_edit_by`) REFERENCES `zym4921_members` (`id`),

CONSTRAINT `zym4921_news_ibfk_3` FOREIGN KEY (`countryid`) REFERENCES `zym4921_countries` (`id`),

CONSTRAINT `zym4921_news_ibfk_4` FOREIGN KEY (`gameid`) REFERENCES `zym4921_games` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

i modified the KEY `type_status` (`type`,`status`), to

KEY `type_status` (`type`,`status`,`date`), and

KEY `type_status` (`date`,`type`,`status`)

no success at all.

version is 5.0.27-community-log

fenway · September 5, 2008

That's a good start.. any sample data?

satanik · September 6, 2008

CREATE TABLE `zym4921_countries` (
`id` int unsigned NOT NULL auto_increment PRIMARY KEY ,
`prefix` varchar(3) NOT NULL UNIQUE,
`country` varchar(50) NOT NULL UNIQUE
) ENGINE = innodb;

CREATE TABLE `zym4921_games` (
`id` int unsigned NOT NULL auto_increment PRIMARY KEY ,
`image` varchar(250) NOT NULL UNIQUE,
`game` varchar(50) NOT NULL UNIQUE
) ENGINE = innodb;

INSERT INTO `zym4921_games` (`id`, `image`, `game`) VALUES 
(1, '/images/icons/games/cs.gif', 'Counter-Strike 1.6'),
(2, '/images/icons/games/quake.gif', 'Quake 4'),
(3, '/images/icons/games/css.gif', 'Counter-Strike Source'),
(4, '/images/icons/games/wc3.gif', 'WarCraft 3'),
(5, '/images/icons/games/cod.gif', 'Call of Duty 2');

INSERT INTO zym4921_countries (prefix,country) VALUES ('ad','Andorra'),('ae','United Arab Emirates'),('af','Afghanistan'),('ag','Antigua and Barbuda'),('ai','Anguilla'),('al','Albania'),('am','Armenia'),('an','Netherlands Antilles'),('ao','Angola'),('aq','Antartica'),('ar','Argentina'),('as','America Samoa'),('at','Austria'),('au','Australia'),('aw','Aruba'),('ax','Aland Islands'),('az','Azerbaijan'),('ba','Bosnia and Herzegovina'),('bb','Barbados'),('bd','Bangladesh'),('be','Belgium'),('bf','Burkina Faso'),('bg','Bulgaria'),('bh','Bahrain'),('bi','Burundi'),('bj','Benin'),('bl','Saint Barthélemy'),('bm','Bermuda'),('bn','Brunei Darussalam'),('bo','Bolivia'),('br','Brazil'),('bs','Bahamas'),('bt','Bhutan'),('bv','Bouvet Island'),('bw','Botswana'),('by','Belarus'),('bz','Belize'),('ca','Canada'),('cc','Cocos (Keeling) Islands'),('cd','Congo, The Democratic Republic of'),('cf','Central African Republic'),('cg','Congo'),('ch','Switzerland'),('ci','Cote d´Ivoire'),('ck','Cook Island'),('cl','Chile'),('cm','Cameroon'),('cn','China'),('co','Colombia'),('cr','Costa Rica'),('cu','Cuba'),('cv','Cape Verde'),('cx','Christmas Island'),('cy','Cyprus');

CREATE TABLE `zym4921_members` (
  `id` int unsigned NOT NULL auto_increment PRIMARY KEY,
  `username` varchar(30) NOT NULL UNIQUE,
  `password` char(64) NOT NULL,
  `level` ENUM('1','2','3','4','5','6') NOT NULL default '5',
  `confirmed` varchar(32) NOT NULL,
  `nr_logins` int unsigned NOT NULL default 0,
  `member_since` int unsigned NOT NULL,
  `pwdchange_token` char(32) default NULL,
  `last_login` int unsigned default NULL,
  `last_seen` int unsigned NOT NULL,
  `last_attempt` int unsigned default NULL,
  `nr_attempts` tinyint(1) unsigned NOT NULL default 0
) ENGINE = innodb;

INSERT INTO `zym4921_members` (`id`, `username`, `password`, `level`, `confirmed`, `nr_logins`, `member_since`, `pwdchange_token`, `last_login`, `last_seen`, `last_attempt`, `nr_attempts`) VALUES 
(1, 'u1', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 0, 0, '150772', 28216, 4294967295, NULL, 0),
(2, 'u2', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 67, 0, '515544', 28038, 4294967295, NULL, 0),
(3, 'u3', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 51, 0, '874232', 32581, 4294967295, NULL, 0),
(4, 'u4', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 15, 0, '382786', 89420, 4294967295, NULL, 0),
(5, 'u5', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 53, 0, '635932', 28718, 4294967295, NULL, 0),
(6, 'u6', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 40, 0, '509163', 12119, 4294967295, NULL, 0),
(7, 'u7', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 60, 0, '940422', 34135, 4294967295, NULL, 0),
(8, 'u8', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 98, 0, '390511', 442, 4294967295, NULL, 0),
(9, 'u9', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 95, 0, '669111', 22406, 4294967295, NULL, 0),
(10, 'u10', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 830290, 3149440, '41712799', 628745, 4294967295, 842337, 5),
(11, 'u11', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 52, 0, '871375', 31067, 4294967295, NULL, 0),
(12, 'u12', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 75, 0, '725161', 6713, 4294967295, NULL, 0),
(13, 'u13', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 41, 0, '336753', 99682, 4294967295, NULL, 0),
(14, 'u14', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 781274, 6192809, '2356968', 249186, 4294967295, 222751, 5),
(15, 'u15', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 72, 0, '813350', 85389, 4294967295, NULL, 0),
(16, 'u16', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 0, 0, '366835', 64355, 4294967295, NULL, 0),
(17, 'u17', '89aa4e14d3e08bfad255fc0f2772e99ee88f0375ce6785371cb1f68999271d74', '5', '1', 38, 0, '550699', 73221, 4294967295, NULL, 0);

CREATE TABLE `zym4921_news` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `userid` int(10) unsigned NOT NULL,
  `gameid` int(10) unsigned NOT NULL,
  `countryid` int(10) unsigned NOT NULL,
  `title` varchar(50) NOT NULL,
  `intro` varchar(1000) NOT NULL,
  `more` text NOT NULL,
  `banner` varchar(200) default NULL,
  `date` int(10) unsigned NOT NULL,
  `last_edit_date` int(10) unsigned default NULL,
  `last_edit_by` int(10) unsigned default NULL,
  `status` enum('1','2','3') NOT NULL,
  `type` enum('1','2') NOT NULL,
  `no_comments` int(10) unsigned NOT NULL default '0',
  `no_visits` int(10) unsigned NOT NULL default '0',
  `no_votes` int(10) unsigned NOT NULL default '0',
  `rate` double unsigned default NULL,
  PRIMARY KEY  (`id`),
  KEY `userid` (`userid`),
  KEY `last_edit_by` (`last_edit_by`),
  KEY `countryid` (`countryid`),
  KEY `gameid` (`gameid`),
  KEY `type_status` (`type`,`status`),
  CONSTRAINT `zym4921_news_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `zym4921_members` (`id`),
  CONSTRAINT `zym4921_news_ibfk_2` FOREIGN KEY (`last_edit_by`) REFERENCES `zym4921_members` (`id`),
  CONSTRAINT `zym4921_news_ibfk_3` FOREIGN KEY (`countryid`) REFERENCES `zym4921_countries` (`id`),
  CONSTRAINT `zym4921_news_ibfk_4` FOREIGN KEY (`gameid`) REFERENCES `zym4921_games` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `zym4921_news` (`id`, `userid`, `gameid`, `countryid`, `title`, `intro`, `more`, `banner`, `date`, `last_edit_date`, `last_edit_by`, `status`, `type`, `no_comments`, `no_visits`, `no_votes`, `rate`) VALUES 
(1, 3, 5, 2, 'DvFnAievmqenhHjcqaoyyjEAaHsgHEyqxeh', 'xeFurfndCtbiFhEvsqokBesFstzsDvbzuvCvDpeBpnpnsmwFDvisvchBtccCEoygmqvadcBEFuxDHCiubfHdkoCcrGirBuicHxjB', 'more', 'ugenhutkHykxcnjdgGHwuinFGwEasHtsatB', 171500, 634859, NULL, '3', '1', 0, 0, 0, NULL),
(2, 1, 2, 5, 'twcypGacfDfgCvmdbEGgDFBrFqhpjxcHumy', 'DrAqvytsgjpicjwtfmEzHroxroEyfeuwGigDkEGgivtHhticgjzBvEibrGqdiuwDEorvEqihgnucihAGcvqievGAmDFEhpupzExz', 'more', '', 493262, NULL, NULL, '3', '2', 0, 0, 0, NULL),
(3, 6, 4, 2, 'yusxoGzsdocgHBtiApvCyhhrspEnoHtacfw', 'HpiquyjHeCmhgqCyFEsnDBCydotwiocxpcfAgdinwxeroueusHfvwvrbByaDwGcrhvqGfnuxtpiwHtCmsGezBksjEEDFonpgoafa', 'more', 'ecdvurAjwtAvdixombuBhEeubimFxeCtpGy', 925251, 790229, NULL, '3', '2', 0, 0, 0, NULL),
(4, 10, 1, 1, 'sBqhswrpyuCwxkCksckpCtvqzHvmBcvudcr', 'CvfHyobDzHdyEadBwshHHehwkFjvwseiqHamyGvjCqfEDeoshcsbEDhzmuAstDDxGzzctDccCjdrywtxxaxqyGehivfBktrrmDkg', 'more', '', 689231, NULL, NULL, '2', '2', 0, 0, 0, NULL),
(5, 5, 4, 6, 'afFnjoehmAnxafgswdzFdssvxFzDiiEjfae', 'DdxocxwxvAuEfdDrADkrbCkkcfwGquyEjmkbefCFeicrCkohntwckzgrAbCrkhFsbrmtusocHuFHEnkCkensGspuCCnGszscgqzG', 'more', 'owqkqGgGEEcefvmxhxyFugoygzfosofCsBf', 374836, 282735, NULL, '3', '2', 0, 0, 0, NULL),
(6, 2, 1, 2, 'pvhvugCaiqHgqapsvGoDxEusgDimbrcyzFi', 'ExbEGHiqBdesrhraycxpqEsxompkuyghvxeFurfndCtbiFhEvsqokBesFstzsDvbzuvCvDpeBpnpnsmwFDvisvchBtccCEoygmqv', 'more', '', 168065, NULL, NULL, '3', '2', 0, 0, 0, NULL),
(7, 8, 4, 3, 'FCobAngAHErfAhkCCxcFpdBGiEEcsDoCHmy', 'BBfctxxhiFymFeHCGpxqnotwweCfzghodxFgyhxEaFxmbkxDgsDjqfHamkiCBsmeiHszygsHwjsyaDiijaFrpkjAFDsEyinhjvbv', 'more', 'FpahtpfrAwezBFCrouzEArDsuHuqeEHkdup', 36794, 738197, NULL, '2', '1', 0, 0, 0, NULL),
(8, 9, 4, 4, 'fCwnufopwxykrtnaxtzakEwhHiqgBEbCksx', 'BFCyabzjtotzovGnbBgcprHfbCssCntpjijBmbpFrChwyhmmqkcDyydrFpvecgndvHuitpHFkwzGEhvxzhCHDicgxpudgmceDcAc', 'more', '', 643430, NULL, NULL, '3', '1', 0, 0, 0, NULL),
(9, 3, 2, 1, 'oEayxgipuCpxfbppbtuejuyBsDgCCtCiApa', 'DGCHbHxEdquahhrFexDycuxGzmnBnAGAwhEbCseevbefhvEznvDGjCbnyvDeBCjBkCCqrqpzaatxdofGrFAtnpjfsjgCdkrfDDwi', 'more', 'HvwebAggugaoeamuuBFeqxGCxgecAqzxwdo', 248494, 287960, NULL, '2', '2', 0, 0, 0, NULL),
(10, 1, 5, 1, 'dinwxeroueusHfvwvrbByaDwGcrhvqGfnux', 'tkgFbFGDdAzDbmxezvmnraBCgtxgfxcxhntbAchrvByzdHbHcuDGEeiEosCrEivHjjjcDeEHryaqtrBxCqBoxddCHmkwbxteydnG', 'more', '', 879003, NULL, NULL, '3', '2', 0, 0, 0, NULL);

run the queries by this order

fenway · September 6, 2008

I honestly have no idea.... I ran this query:

SELECT n.title,n.id,n.no_comments

FROM zym4921_news AS n

WHERE n.type="1" AND n.status="1"

ORDER BY n.date

DESC LIMIT 6

With only an index on date, and it's perfect... but as soon as you join in the other table, it blows up. Apparently, mysql 5.1 allows you to use an index hint specifically for order by -- but this seems like a bug to me.

satanik · September 7, 2008

exactly, i noticed also that the news table alone uses the index but with join it refuses the order by index..

anyway, should i report it to mysql or is fixed in any new versions?

satanik · September 7, 2008

updated mysql to the newest version and it fixed the issue.

Sign In

[SOLVED] COUNT + INDEXES

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information