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
– Michael Berkowski, Commented Jul 28, 2012 at 22:24
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — Madara's Ghost
– Madara's Ghost, Commented Jul 28, 2012 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
– Michael Berkowski, Commented Jul 28, 2012 at 22:26
@Michael: I use stackapps.com/questions/2116/…. Happy commenting! :D — Madara's Ghost
– Madara's Ghost, Commented Jul 28, 2012 at 22:26

Madara's Ghost · Accepted Answer · 2012-07-28 22:24:42Z

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.

Zane Bien · Accepted Answer · 2012-07-28 23:08:45Z

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 · Accepted Answer · 2012-07-29 00:07:44Z

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

Arkheart · Accepted Answer · 2012-07-28 22:30:16Z

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.

Skrol29 · Accepted Answer · 2012-07-28 22:31:32Z

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'");

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Linked

Related