There is no magic solution to count table contents especially if you have a client that has a table with 40+ million records and you need to do the filtering using the ‘OR‘ SQL statement.
Originally this article was published on codeboost.com domain.
The original query was the following:
SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2') OR field2 IN ('val3','val4');
The first benchmark before doing optimization showed me results after 4 minutes.
My first step was to create a compound key (multi-column key) using the field1 and field2. I created it as following:
ALTER TABLE table1 ADD INDEX `field1_field2_idx` (`field1`,`field2`);
After waiting for 3 hours (you remember I had a table with 40 million records) I was able to continue.
This new compound key does not give me any performance increase because the OR statement was used in the original query.
As some of you know “ORs are notoriously bad performers because it splinters the execution path” (from http://stackoverflow.com/questions/6551682/mysql-indexing-in-an-or-statement).
After some thinking, I decided to get rid of the OR and create 2 queries instead.
The following 2 queries gave me the same result ( I had the sum up the results):
SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2') AND field2 NOT IN ('val3','val4'); SELECT COUNT(*) from table1 WHERE field2 IN ('val3','val4');
Now the results were ready after 2 minutes. The first query was almost instant because it used the compound key and the second query took all the time. It was because the index was missing for field2.
So, as a final step, I had to add another index on field2. I used the following command:
ALTER TABLE table1 ADD INDEX `field2_idx` (`field2`);
I had to wait another 3 hours for the index to be ready and finally:
JUST 5 SECS to count rows on 40+ million records’ table result was achieved.
Hope you will find this information useful in your case.
In the original article Vladimir Sotirov provided another solution:
you can use the First index only if you just run the SQL
SELECT COUNT(*) from table1 WHERE field1 IN ('val1','val2');
SELECT COUNT(*) from table1 WHERE field2 IN ('val3','val4')
AND field1 NOT IN ('val1','val2');
That will save you having both indexes, but may give you slower performance.
About the author
For the past 15 years I’ve been leading the evolution of startups and enterprises to achieve the highest level of security and compliance. Throughout my career I’ve been a Cyber Security expert and advanced solutions architect with many years of hands on experience both on offensive and defensive side. Knowledgeable at the highest level in application development, networking, data and databases, web applications, large scale Software as a Service solutions, cloud security and blockchain technologies.
I’ve been working with CISO’s of international enterprises, helping them set Information Security strategy, and overseeing the implementation of these recommendations. As part of these projects, I’ve been assisting companies to achieve compliance in GDPR, PCI, HIPAA and SOX.
Among my credits, I was a founder of a database security company GreenSQL/Hexatier which was acquired by Huawei and I’ve co-founded Kesem.io, Secure multi-signature Crypto wallet.
Specialties: Software and cloud architecture, Compliance (GDPR, HIPAA, PCI, SOX), blockchain technologies, software development, secure architectures, project management and low level research.