How to optimize MySQL Querys

Question

I have this table layout:

uuid bigint(20) unsigned primary auto_increment
timestamp int(11) unsigned
name varchar(255)
type enum('A','B')
subtype varchar(255)

And I'm stuck on the query, what I currently use is this:

SELECT name, COUNT(*) as count FROM table GROUP BY name

After this I'm running a loop for each 'name' to get the amount of entries with type 'B' and then again for each subtype. But this end up in about 500k querys and thats too much, there must be an easier way to do this, but I'm really new to this database stuff...

flo · Accepted Answer · 2012-03-26 11:09:06Z

2

You can use multiple values in GROUP BY.

SELECT name, type, subtype, COUNT(*) as count FROM table GROUP BY name, type, subtype

The result then contains one row for each name, type, subtype combination in the database with the corresponding number of entries.

answered Mar 26, 2012 at 11:09

flo

2,01813 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tobias Herkula Over a year ago

I hoped that the resultset can have additional columns instead of more rows.

KingCronus Over a year ago

@Tobias, could you explain what you mean by that?

Tobias Herkula Over a year ago

If i run this query i get up to 20 rows starting with the same name, what I really need is only one row per name and one col for type and one col per subtype.

KingCronus · Accepted Answer · 2012-03-26 11:09:55Z

0

SELECT name, type, subtype, Count(*) FROM table GROUP BY name, type, subtype

How about that?

answered Mar 26, 2012 at 11:09

KingCronus

4,5302 gold badges26 silver badges52 bronze badges

Collectives™ on Stack Overflow

How to optimize MySQL Querys

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related