Count table rows

SELECT COUNT(*) FROM fooTable;

will count the number of rows in the table.

See the reference manual.

Simply:

SELECT COUNT(*) FROM `tablename`

select count(*) from YourTable

If you have several fields in your table and your table is huge, it's better DO NOT USE * because of it load all fields to memory and using the following will have better performance

SELECT COUNT(1) FROM fooTable;

As mentioned by Santosh, I think this query is suitably fast, while not querying all the table.

To return integer result of number of data records, for a specific tablename in a particular database:

select TABLE_ROWS from information_schema.TABLES where TABLE_SCHEMA = 'database' 
AND table_name='tablename';

If you have a primary key or a unique key/index, the faster method possible (Tested with 4 millions row tables)

SHOW INDEXES FROM "database.tablename" WHERE Key_Name=\"PRIMARY\"

and then get cardinality field (it is close to instant)

Times where from 0.4s to 0.0001ms

It can be convenient to select count with filter by indexed field. Try this

EXPLAIN SELECT * FROM table_name WHERE key < anything;