News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

MySQL Count

Started by MrCue, May 28, 2005, 09:23:43 PM

Previous topic - Next topic

MrCue

Im running several queries like the following and they take several seconds each, Us there a way to speed it up, or a better way to write it?

SELECT *, COUNT(pk_corp) as pk_count FROM pk_mails GROUP BY pk_corp ORDER BY pk_count DESC LIMIT 10
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

Well, let's look at this:

SELECT *, COUNT(pk_corp) AS pk_count
FROM pk_mails
GROUP BY pk_corp
ORDER BY pk_count DESC
LIMIT 10;

This is a very complicated query for MySQL.  First, you're ordering by a count.  This forces MySQL to examine a lot more rows than it would have to otherwise.  Group bys are also almost always slow, although a few seconds is bad.

I would check key usage.  What do you get if you run the query with "EXPLAIN " in front of it?  It should tell you the key usage on the pk_mails table.

A possibility here is breaking it up into two tables - a pk_corps and a pk_mail.  I really have no idea what these are, but the idea would be to put the count of pk_mails per pk_corp in the pk_corps table, and make it an index.  You could then order by this, reducing significantly the number of examined rows.

-[Unknown]

MrCue

#2
i wanted to avoid an extra table, it wouldnt let me add an index to the text column, and a fulltext index didnt seem to help any either.

table      type      possible_keys      key      key_len      ref      rows      Extra
pk_mails    ALL    NULL    NULL    NULL    NULL    20888    Using temporary; Using filesort

Each row is a seperate mail, i basicaly need to count which 10 corps have the most rows, and how many they have.
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

If it's a text column, you're going to have fun.  Still, try adding a key like so:

ALTER TABLE pk_mails
ADD INDEX pk_corp(pk_corp(30));

-[Unknown]

MrCue

Added the index. but it hasnt reduced in time.
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

Does the explain show it using the index at all?

-[Unknown]

MrCue

I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

Then it probably can't use an index at all.

How fast is this?

SELECT pk_corp, COUNT(pk_corp) AS pk_count
FROM pk_mails
GROUP BY pk_corp;

-[Unknown]

MrCue

6.6 secs. The original is 7.4
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

What sort of column is pk_corp?  What's the average length?

-[Unknown]

MrCue

longtext
min 4, max 49 (but new enteries may be longer)
average 17.4
I am neither a Pessimist nor an Optimist, Just a Realist.

Eve-Online Forum | View Latest Eve-Online Kills | Site Map | SMF Installation

[Unknown]

That's never going to be fast.

Isn't it possible to, maybe, identify it by a sha1 code or an incrementing number?  Either would be better than trying to sort and group by a longtext!

-[Unknown]

Advertisement: