Getting counts for 10K's of rows fast

gerardg

I've got 100K's of rows, and I need record counts really fast. Right now queries are taking about 15 seconds.

Run periodic count queries and update a count field?

[deleted]

How do you have the table indexed? I have tables in a MSSQL 2000 server with anywhere up to 30,000,000 rows and my counts don't take nearly that long...I would look at your indexing.....

Matt Wade
http://codewalkers.com

gerardg

In one table each of the two columns has it's own index.

Anon

What actual DMBS are you using?

Chris

gerardg

mysql

I'm doing queries like:

select a.id, count(b.id)
from a
left join b on a.id2 = b.id
group by a.id

queries w/o 'group by' are nearly instant.

[deleted]

and you have indexes on both those columns?
and your mysql has sufficient sort-memory?
and what does 'explain' tell you?
and do you analyze your table regularly?

gerardg

and you have indexes on both those columns?
yep.

and your mysql has sufficient sort-memory?
afaik

and what does 'explain' tell you?

+-------+-------+---------------+----------+---------+-------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+-------+---------------+----------+---------+-------+-------+-------------+
| c | index | NULL | PRIMARY | 4 | NULL | 18 | Using index |
| d | ref | c_ID | c_ID | 4 | c.ID | 14168 | Using index |
+-------+-------+---------------+----------+---------+-------+-------+-------------+

and do you analyze your table regularly?
yep

soooo.....?

I think mysql counts each and every record for this type of query.

gerardg

and have you actually tried this query yourself?

Anon

Do you have rough row counts for your a and b tables? I have some header tables with small numbers of rows (sub 100) and a couple of data tables at about 2 million rows and would like to try and sim your problem.

As an example one of the explains to one of the selects I've been trying gives me:

table type possible_keys key key_len ref rows Extra

A index NULL Primary 2 NULL 86 Using index
B ref Sbu Sbu 3 A.sbu_id 23608 Using index

So I think the numbers are fairly comparable.

It's taking about 4 secs per run.

One thing I have noticed is that it appears to run slightly quicker if the indexes are based on numeric IDs.

Cheers
Justin

gerardg

yeah - table "a" has about 25 records and "b" has around 300K. The query takes a second or two. If I join a third table, it increases to about 10 seconds.