How to see percentage of existing values for all columns in mysql table?

I have three large-ish tables (~20 columns each) and I want to know what percentage of rows have a value for each column.

For instance, this table:

ID   |   TITLE            | SERVING     |    NOTE
--------------------------------------------------------------------
6716 | Yummy Molasses ... | 1 cookie    |
3765 | Rosemary-Red Wi... |             |
5178 | Stuffed Avocado... |             |
6025 | Amazing Pea Sou... | about 1 cup |
4412 | Overnight Oatme... | 1 cup       | Note: Steel-cut oats...

Might yield results similar to this:

ID  | TITLE | SERVING | NOTE
-----------------------------
100%| 100%  |  60%    | 20%

I have a Dynamic SQL solution

PROPOSED QUERY

SET group_concat_max_len = 1048576;
SET @GivenDB = 'mydb';
SET @GivenTable = 'mytable';
SELECT CONCAT('SELECT ',GROUP_CONCAT(CONCAT('COUNT(',column_name,
')*100/COUNT(1) ',column_name,'')),
' FROM ',table_schema,'.',table_name) sqlstmt
INTO @sql FROM information_schema.columns
WHERE table_schema=@GivenDB
AND table_name=@GivenTable;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

MySQL Window Functions or Analytic Functions

MySQL 8.0.2 introduces SQL window functions, or analytic functions as they are also sometimes called. They join CTEs (available since 8.0.1) as two of our most requested features, and are long awaited and powerful features. This is the first of a series of posts describing the details. Let’s get started!

 

Introduction

Similar to grouped aggregate functions, window functions perform some calculation on a set of rows, e.g. COUNT or SUM. But where a grouped aggregate collapses this set of rows into a single row, a window function will perform the aggregation for each row in the result set, letting each row retain its identity:

Given this simple table, notice the difference between the two SELECTs:

mysql> CREATE TABLE t(i INT);
mysql> INSERT INTO t VALUES (1),(2),(3),(4);

mysql> SELECT SUM(i) AS sum FROM t;
+——+
| sum |
+——+
| 10 |
+——+

mysql> SELECT i, SUM(i) OVER () AS sum FROM t;
+——+——+
| i | sum |
+——+——+
| 1 | 10 |
| 2 | 10 |
| 3 | 10 |
| 4 | 10 |
+——+——+

 

Read More

MySQL FIND_IN_SET function

MySQL provides a built-in string function called FIND_IN_SET that allows you to find the position of a string within a comma-separated list of strings.

The following illustrates the syntax of the FIND_IN_SET function.

FIND_IN_SET(needle,haystack);

The FIND_IN_SET function accepts two parameters:

  • The first parameter needle is the string that you want to find.
  • The second parameter haystack is a list of comma-separated strings that to be searched.

The FIND_IN_SET function returns an integer or a NULL value depending on the value of the arguments:

  • Return a NULL value if either needle or haystack is NULL.
  • Return zero if the needle is not in the haystack or the haystack is an empty string.
  • Return a positive integer if the needle is in the haystack.

Examples :

The following statement returns 2 because it is the second position of ‘x,y,z’ string.

SELECT FIND_IN_SET(‘y’,’x,y,z’); 

Comparison between Solr and Zend Lucene

Comparison between Solr and Zend Lucene

Java Lucene, and all its ports to other languages, including Zend Lucene, are search libraries.

This means that in order to use Zend Lucene, we have to wrap it in other (PHP) code that will integrate the search with the rest of our application. The code generally needs to manage indexing, retrieval and usually some housekeeping of Lucene. We communicate with Zend Lucene using PHP function calls.

Solr, On the other hand, is a search server built on top of Lucene. This means that a Solr instance can run as a stand-alone server webapp inside a servlet container (That could be Tomcat, Jetty or one of several other such programs). It is much easier to set up a Solr server than a Lucene application. We can do a lot with Solr without writing a single line of Java – just by tweaking some XML configuration files. Setting up a Solr server may take as few as several minutes. The default way to communicate with Solr is using HTTP calls.

So basically Zend Lucene installation requires having a PHP server and proper indexing and retrieval using a PHP library. Solr installation requires running a Java servlet container and deploying a war file into it.

Solr is a better option for text searching in comparison to Zend Lucene. Some limitations of Zend Lucene:

  • It is fit for small websites with fewer amounts of data
  • Creating index from database records need more time
  • When working with large amount of data, index creation is very slow
  • When searching on large index, search performance is poor

 

Integrate PHP application with Solr search engine

Integrate PHP application with Solr search engine

So why do you need a search engine, is database not enough? If you create a small website it might not matter. With medium or big size applications it’s often wiser to go for a search engine. Saying that, even a small websites can benefit from Solr if you desire a high level of relevance in search results.

Let’s imagine you have to create a search handler for an e-commerce website. A naive approach would be creating database query like this:

It might work if the search phrase is exactly as part of a title or a description. In the real life items have complex names, for instance: Apple iPhone 4G black 16GB. If somebody looks for “iPhone 16GB” no results will be returned. You can mitigate it by replacing white spaces with “%” character before the phrase is passed to SQL.

Read More…………..

Search Engine with Zend_Search_Lucene

On several occasions developing database-driven web applications, I’ve been
approached by clients who want Google-style search implemented at the last minute
of the development cycle. Usually this leads to using some canned script that
crawls the website, or a hacked up search function that uses the database but
either returns too many results or none at all. On top of that, the queries
performed are too many or too slow. [More………..]

 

Fetch Total Fifty and Hundred Run in Team player

Table Structure

Image

Query

SELECT player_id,SUM(CASE WHEN total_run>=50 and total_run<100 then 1 else CASE WHEN total_run>=150 and total_run<200 then 1 else 0 end end) AS fity, SUM(CASE WHEN total_run>=100 and total_run<200 THEN 1  ELSE CASE WHEN total_run>=200 and total_run<300 then 1 else 0 end END) as hunder from run WHERE 1 group by `player_id`

Result:

Image

Table Structure