Count On Bigest tables

my problem is caused when i try to get the COUNT of a consult return a big amount of records (example 500.000):

Example:

$limit = ' LIMIT 0,30';
$ResultQuery = mysql_query("
    SELECT id, name, description, date 
    FROM products 
    WHERE name 
    LIKE 'dog%'".$limit);

$CountQuery = mysql_query("
    SELECT COUNT(id) 
    FROM products 
    WHERE name LIKE 'dog%'");

while ($Product = mysql_fetch_assoc($ResultQuery)) { [...]

NOTE: The Use of COUNT(id) its more fast (in my case) than use mysql_num_rows of $ResultQuery.

If i see what is doing the server using the MySQL Administrator, i see 3 seconds make the 1º Query (the limit), one second "sending data", and 143 seconds "sending data" of the 2º Query.

I read more articles about this problem its caused because for get the count of the query, need to scan ALL ROWS (without the limit) of the Query.

Not exists any method or mode to bypass this problem? Any method to extract the count of rows with big number of results?

Thanks for the help.

My best bet is you're not having set your indexes up correctly. By the looks of it, you haven't set up a proper index for your name field and that causes MySQL to go through every row (more than 22 000 000) to look for 'dog%'.

I suggest you try using a regular index and benchmark the results

CREATE INDEX `idx_name` ON products (name)

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | products | ALL | name | NULL | NULL | NULL | 22160980 | Using where

You can see from the above that the index "name" is not being used. And the reason for that is the following.

You are using Full Text index on the name column which is only useful for Full-text search queries http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

If you do not do any full-text searches, delete that index and create another index as suggested above

CREATE INDEX idx_name ON products (name)

This will create a BTREE index on name which will then be used in your search query. Note that this index will not be useful if you do a like query with '%' at the beginning. For example

SELECT count(id) FROM products WHERE name LIKE '%dogs'

will not use the index.

$count =  mysql_num_rows($ResultQuery);