2 Replies - 190 Views - Last Post: 10 July 2019 - 09:00 AM

#1 pfar54   User is offline

  • D.I.C Addict

Reputation: 1
  • View blog
  • Posts: 615
  • Joined: 30-April 15

Optimizing multiple select count queries from one database table

Posted 10 July 2019 - 08:43 AM

I have several queries shown below that are all connecting to the same data base table. In addition, they are all select queries that count specific criteria. Out of curiousity, is there a way to optimize all of these queries without changing the db structure for better performance?

I am also running into somewhat of an issue with two of the queries. Query number 1 counts all the records in the database table, which comes outs to 10,650. Query number 3 and 4 are counting the different types of views based on what viewing button was selected and the type of catalog. These views are 10,300 and 10,100. This makes no sense because there are so close to the total count. The majority of views are from the "Profile" catalog. However, the count totals for these two queries should be something like 5,000 and 5,000.

Any ideas for either topic?

// 1. Query to select all catalogs
try {
	$con = getConfig('pdo');
	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$sql_catalog_total = "
		SELECT count(*)
		FROM catalog_download_now
	";
	$total_result = $con->prepare($sql_catalog_total);
	$total_result->execute();
	$rowCountTotal = $total_result->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// 2. Query to select all Profile Catalog Views
try {
	$sql_profile_total = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Profile'
		OR catalog_name='Profile Catalog'
		OR catalog_name='Profile_Catalog'
	";
	$total_profile_result = $con->prepare($sql_profile_total);
	$total_profile_result->execute();
	$rowCountProfile = $total_profile_result->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}


// 3. Query to select all Profile Popular Catalog Views
try {
	$sql_profile_popular = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Profile'
		OR catalog_name='Profile Catalog'
		OR catalog_name='Profile_Catalog'
		AND button_triggered='Profile Popular Button'
	";
	$total_profile_popular = $con->prepare($sql_profile_popular);
	$total_profile_popular->execute();
	$rowCountProfilePopular = $total_profile_popular->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// 4. Query to select all Profile Catalog Product Specific Views
try {
	$sql_profile_prodSpec = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Profile'
		OR catalog_name='Profile Catalog'
		OR catalog_name='Profile_Catalog'
		AND button_triggered='Profile - Product Specific'
	";
	$total_profile_prodSpec = $con->prepare($sql_profile_prodSpec);
	$total_profile_prodSpec->execute();
	$rowCountProfileProdSpec = $total_profile_prodSpec->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}


// 5. Query to select all Fastening Tech Catalog Views
try {
	$sql_fastTech = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Fastening Technology'
	";
	$total_fastTech = $con->prepare($sql_fastTech);
	$total_fastTech->execute();
	$rowCountFastTech = $total_fastTech->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}


// 6. Query to select all Fastening Tech Catalog Popular Views
try {
	$sql_fastTech_popular = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Fastening Technology'
		AND button_triggered='Fast Tech Popular Button'
	";
	$total_fastTech_popular = $con->prepare($sql_fastTech_popular);
	$total_fastTech_popular->execute();
	$rowCountFastTechPopular = $total_fastTech_popular->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// 7. Query to select all Fastening Tech Catalog Product Specific Views
try {
	$sql_fastTech_prodSpec = "
		SELECT count(*)
		FROM catalog_download_now
		WHERE catalog_name='Fastening Technology'
		AND button_triggered='Fastening Technology - Product Specific'
	";
	$total_fastTech_prodSpec = $con->prepare($sql_fastTech_prodSpec);
	$total_fastTech_prodSpec->execute();
	$rowCountFastTechProdSpec = $total_fastTech_prodSpec->fetchColumn();
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

	echo json_encode(['totalCount' => $rowCountTotal, 'totalProfile' => $rowCountProfile, 'totalProfilePopular' => $rowCountProfilePopular, 'totalProfileProdSpec' => $rowCountProfileProdSpec, 'totalFastTech' => $rowCountFastTech, 'totalFastTechPopular' => $rowCountFastTechPopular, 'totalFastTechProdSpec' => $rowCountFastTechProdSpec]);


Is This A Good Question/Topic? 0
  • +

Replies To: Optimizing multiple select count queries from one database table

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15359
  • View blog
  • Posts: 61,588
  • Joined: 12-June 08

Re: Optimizing multiple select count queries from one database table

Posted 10 July 2019 - 08:49 AM

I doubt any of those are a performance issue, but yeah.. a few things.

I would suggest 'with nolocks' on all the tables.
Your queries are pretty simple, but counting on a specific key column is a skosh better.
Searching on numbers are better than strings. (ex lines 22-24).. so you could abstract those out.
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7501
  • View blog
  • Posts: 15,544
  • Joined: 16-October 07

Re: Optimizing multiple select count queries from one database table

Posted 10 July 2019 - 09:00 AM

First, best, option is to use bind variables: https://www.php.net/...t.bindparam.php

In hard coding you're forcing the database to reparse for every case. Note, you'll almost certainly want an index on catalog_name, in any case.

Wait, you have ten thousand rows with catalog_name just being a varchar across all of them. Right, next, normalization is your friend. https://en.wikipedia...e_normalization You'll want a catalog table and then use catalog_id in that larger table.

Given what you have, you might do better with something catchall and figuring it out programmatically. e.g.
SELECT catalog_name, count(*) as catalog_count
  FROM catalog_download_now
  GROUP BY catalog_name


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1