what is the fastest way to count a large table using mysql

Question

I finding trouble deciding what is the fastest way to count some specifc records from my tables filtered by a where statment here is my code, here are the queries:

$type_mo3ln_malk = mysql_num_rows(mysql_query("SELECT * FROM `tableshow` $weress AND mo3ln_type='malk'"));
$type_mo3ln_mswg = mysql_num_rows(mysql_query("SELECT * FROM `tableshow` $weress AND mo3ln_type='mswg'"));
$type_mo3ln_mktb = mysql_num_rows(mysql_query("SELECT * FROM `tableshow` $weress AND mo3ln_type='mktb'"));
$type_mo3ln_wkeel = mysql_num_rows(mysql_query("SELECT * FROM `tableshow` $weress AND mo3ln_type='wkeel'"));
$type_mo3ln_no = mysql_num_rows(mysql_query("SELECT * FROM `tableshow` $weress AND mo3ln_type='0'"));

the function that do the count are the one which is predefined in php

I am thinking of using count() function but I am wondering if I could use Sum function for some specific rows only as Mr.Hates suggested here Get record counts for all tables in MySQL database

Whenever the question goes like _is a faster or b_, the only real answer is to setup a test and benchmark it for yourself. The RDBMS is highly optimized already for performing aggregate functions like `COUNT()`, so it is possible that the aggregate will be faster than calling `mysql_num_rows()`. But that also depends on your table's indexing, size, and other factors. — Michael Berkowski, Jul 28 '12 at 22:24
Welcome to Stack Overflow! Please, don't use `mysql_*` functions to write new code. They are no longer maintained and the community has begun [deprecation process](http://goo.gl/KJveJ). See the *[red box](http://goo.gl/GPmFd)*? Instead you should learn about [prepared statements](http://goo.gl/vn8zQ) and use either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). If you can't decide which, [this article](http://goo.gl/3gqF9) will help you. If you pick PDO, [here is good tutorial](http://goo.gl/vFWnC). — Madara's Ghost, Jul 28 '12 at 22:24
@Truth Did you build a browser plugin that detects mysql_*() and auto-inserts that comment or something? :) If so, post it to stackapps! — Michael Berkowski, Jul 28 '12 at 22:26
@Michael: I use http://stackapps.com/questions/2116/autoreviewcomments-pro-forma-comments-for-se. Happy commenting! :D — Madara's Ghost, Jul 28 '12 at 22:26

score 5 · Answer 1 · answered Jul 28 '12 at 22:24

5

SELECT COUNT(*) FROM `table` WHERE `whatever`='whatever';

Will return a single row (instead of over 9000), containing the correct count based on the rules you've set.

answered Jul 28 '12 at 22:24

Madara's Ghost

172,118
50
264
308

Zane Bien · Answer 2 · 2012-07-28T23:08:45.630

This should be even faster:

SELECT COUNT(CASE mo3ln_type WHEN 'malk'  THEN 1 END) AS cnt_mo3ln_malk,
       COUNT(CASE mo3ln_type WHEN 'mswg'  THEN 1 END) AS cnt_mo3ln_mswg,
       COUNT(CASE mo3ln_type WHEN 'mktb'  THEN 1 END) AS cnt_mo3ln_mktb,
       COUNT(CASE mo3ln_type WHEN 'wkeel' THEN 1 END) AS cnt_mo3ln_wkeel,
       COUNT(CASE mo3ln_type WHEN '0'     THEN 1 END) AS cnt_mo3ln_no
FROM   tableshow
WHERE  mo3ln_type IN ('malk', 'mswg', 'mktb', 'wkeel', '0')

This will get all of your counts in one single SELECT statement rather than five separate statements.

Provided that you have an index set up on the mo3ln_type column, the WHERE clause should narrow down your rows fairly quickly, then it's just a matter of conditionally aggregating those rows within COUNT().

It would be even faster if you represented each mo3ln_type as an integer instead of a string as the comparisons on numbers is much faster. Perhaps you can create another table storing the different mo3ln_types and in the tableshow table, just have a foreign key reference to the INT primary key in mo3ln_types instead of the actual text string. (e.g. WHERE mo3ln_type_id IN (1, 2, ...) instead of WHERE mo3ln_type IN 'malk', 'mswg', ...).

alek13 · Answer 3 · 2012-07-29T00:07:44.270

You can use count(*):

SELECT COUNT(*) FROM <table_name> WHERE <where_clause>;

Or (if you make pagination, for example) use SQL_CALC_FOUND_ROWS, if you want also make query:

SELECT SQL_CALC_FOUND_ROWS * FROM <table_name> WHERE <clause> LIMIT 0, 10;
SELECT FOUND_ROWS();

more info: MySql Information Functions -> FOUND_ROWS()

And if you want to get only counts use the SUM function:

SELECT SUM(CASE WHEN mo3ln_type = 'malk'  THEN 1 ELSE 0 END) AS malk,
       SUM(CASE WHEN mo3ln_type = 'mswg'  THEN 1 ELSE 0 END) AS mswg,
       SUM(CASE WHEN mo3ln_type = 'mktb'  THEN 1 ELSE 0 END) AS mktb,
       SUM(CASE WHEN mo3ln_type = 'wkeel' THEN 1 ELSE 0 END) AS wkeel,
       SUM(CASE WHEN mo3ln_type = '0'     THEN 1 ELSE 0 END) AS no_type
FROM   tableshow
WHERE  <where_clausw>

more info: MySql Control Flow Functions -> CASE operator, MySql Aggregate Functions -> SUM function *

dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_sum

score 0 · Answer 4 · answered Jul 28 '12 at 22:30

0

A database always preforms work much faster than the application layer, thus returning the count from the database using the Count(*) command is much more efficient.

answered Jul 28 '12 at 22:30

Arkheart

36
4

score 0 · Answer 5 · answered Jul 28 '12 at 22:31

Ask the database to count the rows, not PHP. Use COUNT(*) in the SQL statement.

function f_count_rows($table, $where='') {
  $result = mysql_query("SELECT COUNT(*) AS nbr FROM `".$table."` ".$where);
  $row = mysql_fetch_assoc($result);
  return $row['nbr'];
}

$type_mo3ln_malk  = f_count_rows("tableshow", $weress." AND mo3ln_type='malk'");
$type_mo3ln_mswg  = f_count_rows("tableshow", $weress." AND mo3ln_type='mswg'");
$type_mo3ln_mktb  = f_count_rows("tableshow", $weress." AND mo3ln_type='mktb'");
$type_mo3ln_wkeel = f_count_rows("tableshow", $weress." AND mo3ln_type='wkeel'");
$type_mo3ln_no    = f_count_rows("tableshow", $weress." AND mo3ln_type='0'");

what is the fastest way to count a large table using mysql

5 Answers5