Topic: Large table and query time

[jaymc]

Im having some trouble with a big table I have

It has 4.7 million records and is heavily accessed

It has the following fields

id = PRIMARY
user = varchar25 INDEX
friend = varchar25 INDEX
timestamp = int12
gender = varchar6
x = tinyint
real = enum 0,1

I cant prune redundant records because.. well basically there is none

Running SELECT count(id) FROM table GROUP BY user takes 5 minutes+
I deleted an index on the x colum and that took 28 minutes to complete (ALTER TABLE)[/b]

What options do I have in circumstances of naturally large tables


The example of SELECT count(id) FROM table GROUP BY user above, is there anything I can do to get something
like that complete after 5 seconds?

[Xurion]

What type of engine is the table on? Myisam? Innodb?

[jaymc]

Sorry

INNODB

[fenway]

Why not a covering index on ( user, id )?

[jaymc]

Sorry, bad example

id is never ever used, apart from in my sample query.. so a covering index on that would prove useless

[fenway]

Sorry, bad example

id is never ever used, apart from in my sample query.. so a covering index on that would prove useless

I don't understand.

[jaymc]

on my website I never use the id field

however, in the example I gave you above of a bad query I used the id field leading you to believe i need a covering index on it

[fenway]

on my website I never use the id field

however, in the example I gave you above of a bad query I used the id field leading you to believe i need a covering index on it

Why are we talking about imaginary queries?  I still don't understand.

[jaymc]

Well I want to know why this

SELECT count(id) FROM table GROUP BY user

takes over 5 minutes to complete when id is the primary key

[fenway]

Why count(id) and not count(*)?

[awpti]

Why are you using a group by?

That makes no sense.

SELECT COUNT(*) FROM my_table; will return the same total number.

How are you defining which group a given count belongs to?

I'd suggest creating an index:

 ALTER TABLE `my_table` ADD INDEX ( `user` ( 25 ) ) ;

Reduced the count query time from 1m 32s to ~20sec. I'm guessing your db server isn't a terribly beefy machine.

As a side note, I forced in double the amount of records you have, all with random data.

[toplay]

You could run optimize periodically:

http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

and:

http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB and BDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

[jaymc]

I dont think optimize will help as they are INNODB tables thus zero/little overhead. I've only experienced fregmented tables with MyIsam

If ran optimize on the 4.7 million rows table it would take about 10-20 minutes

I already have an index on user

SELECT count(*) FROM table GROUP BY user
This is an example of a query that is taking 400+ seconds to complete. Ignore the relivence of it, as its only an example and not something I ever use, but if I can find out why a simple query like this takes so long..

[Hooker]

Doing a "SELECT Count(*) FROM table" on InnoDB and requires a full table scan. (With MyIsam this operation doesn't cost anything because MyIsam stores an internal record counter with each table).

[awpti]

Your DB design here is full of fail.

Do you handle tons of inserts/updates to this table? If not, switch the engine to isam.