Topic: SELECT COUNT(*) GOOD IDEA?

[Azaz]

Code: [Select]

SELECT count(*) as total FROM forum_replies
My forum_replies has over 30,000 rows... how would mysql handle this on a forum on the main index, is there a better/simplified way ?

I just need to grab posts not *... ?

[Mchl]

Is it MyISAM or InnoDB table? For MyISAM this is extremely fast, because it stores the rowcount alongside the table.

[Azaz]

MYIASM, but i heard you can use

Code: [Select]

COUNT("pid") or just count 1 row instead of using  * still possible faster ? thanks[/code]

[Mchl]

Not really. MySQL is coded to recognise SELECT COUNT(*) FROM tableName query and return stored rowcount. I'm not sure if this optimisation holds if you substitute * with actual field. Even if it does, there'd be no difference.

Also notice that this optimisation will only be in effect, when there's no WHERE clause in the query (i.e. you want to count all rows in the table)

[Azaz]

Not really. MySQL is coded to recognise SELECT COUNT(*) FROM tableName query and return stored rowcount. I'm not sure if this optimisation holds if you substitute * with actual field. Even if it does, there'd be no difference.

Also notice that this optimisation will only be in effect, when there's no WHERE clause in the query (i.e. you want to count all rows in the table)

Sweet, Solved.

Gotta love people like you here, I hope to hear from you in the future,