I think, therefore I am. I am, therefore I sail

Category: MySQL Page 3 of 4

MySQL: Show count of records in each table of a database

I recently did a dump of my database on this site and it was almost 300 megabytes! That is pretty huge for a simple personal site. Curious as to where all that data was hiding I whipped up a quick SQL query that calculates the total number of rows per table in a single database. Here is the query for your enjoyment ( and so I remember it later! )

SELECT TABLE_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'db21288_ben' 
ORDER BY `TABLES`.`TABLE_ROWS`  DESC

MySQL: Count number of columns in a table

Counting the number of columns, or fields, in a MySQL table is a fairly simple, though not exactly obvious task. You will need to know both the name of the table to query on, as well as the database. The query is as follows, plug in your values for database_name and table_name

SELECT COUNT(*)
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE table_schema = 'database_name'
             AND table_name = 'tbl_name'

Typo3: Count the number of Pages, Content, and Words in your install


Warning: DOMDocument::loadHTML(): Unexpected end tag : b in Entity, line: 41 in /srv/users/serverpilot/apps/benlobaughnet/public/blog/wp-content/plugins/auto-amazon-links/amazon.php on line 78

Ever wonder how many pages, words or tt_content element you have in your Typo3 install? I am working with two large Typo3 sites and I was curious so I wrote a little piece of code. It works really well and is super quick. Here is what the output looks like:

Counting Pages
SELECT COUNT(uid) AS num_pages FROM `pages`Counting Pages Not Deleted
SELECT COUNT(uid) AS num_pages FROM `pages` WHERE deleted=’0′

Counting Content
SELECT COUNT(uid) AS num_content FROM `tt_content`

Counting Words
SELECT bodytext FROM `tt_content`

Totals
Num Pages: 4505
Num Pages Not Deleted: 2948
Num Content: 5152
Num Words: 28,394,588

If you would like to implement this yourself I provided the code below, instead of reinventing the wheel yourself.

(I put mine in the typo3conf folder for ease of use)

Counting Pages';
echo '
'.$sql_count_pages; $result = $mysqli->query($sql_count_pages); $result = $result->fetch_assoc(); $num_pages = $result['num_pages']; echo 'Counting Pages Not Deleted'; echo '
'.$sql_count_pages_not_deleted; $result = $mysqli->query($sql_count_pages_not_deleted); $result = $result->fetch_assoc(); $num_pages_not_deleted = $result['num_pages']; echo '

Counting Content'; echo '
'.$sql_count_content; $result = $mysqli->query($sql_count_content); $result = $result->fetch_assoc(); $num_content = $result['num_content']; echo '

Counting Words'; echo '
'.$sql_bodytext; $result = $mysqli->query($sql_bodytext); while($r = $result->fetch_assoc()) { $num_words += str_word_count($r['bodytext']); } echo '


Totals'; echo '
Num Pages: ' . $num_pages; echo '
Num Pages Not Deleted: ' . $num_pages_not_deleted; echo '
Num Content: ' . $num_content; echo '
Num Words: ' . number_format($num_words, 0, '.', ','); $mysqli->close(); ?>

PHP: Count words in a MySQL column

Last week I posted a query that will return the number of words in a column of a table in a MySQL database. I was looking through a huge database and the query took a really really long time. Today I decided to rewrite it in PHP and see if it was faster. Much Much Much faster. PHP has a built in function called str_word_count that is super quick at counting. If you have a huge database and access to PHP I definitely recommend this method over the query I  had before.

/* gather the db connection info */
$db_host = 'localhost';
$db_username = '';
$db_password = '';
$db = '';

$mysqli = new mysqli($db_host, $db_username, $db_password, $db);

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* db queries */
$sql_text = "SELECT text_to_look_through FROM `table_containing_text`";

/* useful vars */
$num_words = 0;

echo '

Counting Words'; echo '
'.$sql_text; $result = $mysqli->query($sql_text); while($r = $result->fetch_assoc()) { $num_words += str_word_count($r['bodytext']); } echo '


Totals'; echo '
Num Words: ' . number_format($num_words, 0, '.', ','); $mysqli->close();

MySQL: Count Words in Column

I have been working on a rather large database that contains mostly textual page content. Out of curiosity I decided to see how many words are contained in the column with my content. I did not find a simple MySQL solution to finding the word count, but here is what I did come up with.

SELECT SUM( LENGTH( bodytext ) -  LENGTH( REPLACE( bodytext, ' ', '' ) ) +1 )
FROM tt_content

It worked great on the one page I tried it on. There were 5864 words. It has been running for about 5 minutes on the entire set now. Not the most efficient method out there I am sure, but I found it quick and it works.

To use it replace ‘bodytext’ with the column you are looking in, and ‘tt_content’ with the table the column is in.

Update

The query ran for 40 minutes before I terminated it. If you are using a large dataset I recommend looking at an alternative method, such as using PHP to count words in a column. Use that link to see the method I implemented

Page 3 of 4

Powered by WordPress & Beards