How to determine the collection is available in a table without SELECT iteration in MySQL?

Question

SELECT with WHERE Clause returns a new sequence of items matching the predicate by iteration.

Is there is any way to predict the given Search Criteria is available or not (Boolean) in MySQL?

Sample SQL

CREATE TABLE Ledger
(
PersonID int,
ldate date,    
dr float,
cr float,
bal float
);

INSERT INTO Ledger(PersonID, ldate, dr, cr, bal)
VALUES 
('1001', '2016-01-23', 105 ,0 ,0),
('1001', '2016-01-24', 0, 5.25, 0),
('1002', '2016-01-24', 0, 150, 0),
('1001', '2016-01-25', 0, 15, 0),
('1002', '2016-01-25', 73, 0, 0);

Here I need to Check PersonID 1002 is exist or not

Common Way of Checking is

SELECT COUNT(PersonID) > 0 AS my_bool FROM Ledger WHERE PersonID = 1002

SELECT COUNT(*) > 0 AS my_bool FROM Ledger WHERE ldate = '2016-01-24' AND (bal > 75 AND bal <100)

The above two queries are only for sample.

But, the above SELECT Query iterates the whole collection and filters the result. It degrades the Performance in a very Big Database Collection (over 300k active records).

Is there's a version with a predicate (in which case it returns whether or not any items match) and a version without (in which case it returns whether the query-so-far contains any items).

Here I given a very simple WHERE Clause. But in real scenario there is complex WHERE Clause. .NET there is a method .Any() it predicts the collection instead of .Where().

How to achieve this in a efficient way?

Create index on PersonID in order to minimize the number of rows scan. — 1000111, Feb 03 '16 at 04:59
@SubrataDeyPappu - The above SQL is a sample but in main database the Column is a Indexed Column. Here I given a very simple WHERE Clause. But in real scenario there is complex WHERE Clause. Kindly assist me.. — B.Balamanigandan, Feb 03 '16 at 05:02
If you have too much data you can also consider partitioning of data where particular range of record store into partitions, so only limited data will scanned while searching. — Abhishek Ginani, Feb 03 '16 at 05:12
@B.Balamanigandan: https://dev.mysql.com/doc/refman/5.7/en/partitioning-range.html — Abhishek Ginani, Feb 03 '16 at 05:17
If there are indexes correspond to the WHERE clause it *doesn't* iterate over the entire table, or degrade the performance. That's the *point.* — user207421, Feb 03 '16 at 05:17
@EJP I Can't make each Column to Index. Currently I'm having 3 Lakh active records. — B.Balamanigandan, Feb 03 '16 at 05:20
Yeah...Index will boost the performance but if OP has too much data then can consider partitioning of data to improve performance. — Abhishek Ginani, Feb 03 '16 at 05:20
@EJP In .NET there is a method .Any() it predicts the collection instead of .Where(). I'm asking as like that. Is there is any other way to check? — B.Balamanigandan, Feb 03 '16 at 05:29
@B.Balamanigandan That would correspond to `COUNT(*) > 0` which you're already doing. Still waiting for your WHERE clause ... — user207421, Feb 03 '16 at 05:32

score 1 · Answer 1 · edited May 23 '17 at 11:45

1

You can optimise by using EXISTS

SELECT EXISTS
(
  SELECT 1
  FROM Ledger 
  WHERE ldate = '2016-01-24' 
  AND (bal > 75 AND bal < 100)
) AS my_bool

It will return as soon as a match is found.

Click here to have a play on SQL Fiddle

More info here:

Optimizing Subqueries with EXISTS Strategy

Best way to test if a row exists in a MySQL table

Subqueries with EXISTS vs IN - MySQL

edited May 23 '17 at 11:45

Community

1
1

answered Feb 03 '16 at 08:47

davmos

9,324
4
40
43

How to determine the collection is available in a table without SELECT iteration in MySQL?

1 Answers1