how to count the number of rows fetched from the query consists of data from many tables

Question

I have the following query.

SELECT DISTINCT propertylist.propertyid
    ,propertylist.price
    ,propertylist.publicremarks
    ,address.addressline1
    ,address.streetaddress
    ,address.city
    ,address.postalcode
    ,alternateurl.maplink
    ,building.bathroomtotal
    ,building.bedroomtotal
    ,building.constructeddate
    ,building.sizeinterior
    ,building.type
    ,building.basementfeatures
    ,building.basementtype
    ,building.constructionstyleattachment
    ,propertylist.ammenitiesnearby
    ,propertylist.features
    ,propertylist.transactiontype
    ,propertylist.lastupdated
    ,propertylist.communityfeatures
    ,land.acreage
FROM propertylist
    ,address
    ,building
    ,alternateurl
    ,land
WHERE propertylist.propertyid = address.propertyid
    AND address.propertyid = building.propertyid
    AND building.propertyid = alternateurl.propertyid
    AND alternateurl.propertyid = land.propertyid

I want to know the total number of records that will be derived from this query so that i can implement paging in my website. If i try to execute this without 'limit' it takes so much time and the execution time runs out. The result of Explain sql is

Generation Time: Feb 21, 2015 at 01:06 PM
Generated by: phpMyAdmin 4.2.7.1 / MySQL 5.5.39
SQL query: EXPLAIN SELECT DISTINCT COUNT(*) FROM propertylist , address , building , alternateurl ,land WHERE propertylist.propertyid = address.propertyid AND address.propertyid = building.propertyid AND building.propertyid = alternateurl.propertyid AND alternateurl.propertyid = land.propertyid;
Rows: 5

 Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available.
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  alternateurl    ALL NULL    NULL    NULL    NULL    12947   
1   SIMPLE  address ALL NULL    NULL    NULL    NULL    13338   Using where; Using join buffer
1   SIMPLE  building    ALL NULL    NULL    NULL    NULL    13389   Using where; Using join buffer
1   SIMPLE  propertylist    ALL NULL    NULL    NULL    NULL    13614   Using where; Using join buffer
1   SIMPLE  land    ALL NULL    NULL    NULL    NULL    13851   Using where; Using join buffer

post the result of performing an `EXPLAIN SELECT..`. on your query - while the COUNT(*) answers are correct, you've probably got a very inefficient query — Paul Dixon, Feb 21 '15 at 11:49
Am I correct that there is not a single index on any of those tables and its columns? — rene, Feb 21 '15 at 15:26
That explain tells you no indexes are being used - try `ALTER TABLE propertylist ADD INDEX(propertyid);` and repeat for all your tables - you should see the EXPLAIN showing the indexed get used. What you need a big reduction in the 'rows' column of the explain. — Paul Dixon, Feb 21 '15 at 16:30
@rene yes i haven't applied any index before reading yours and Paul Dixon's comment. Thanks. — Sahil, Feb 22 '15 at 03:18

shauryachats · Accepted Answer · 2015-03-25T03:09:30.487

0

You can modify your query using COUNT:

SELECT DISTINCT COUNT(*) 
FROM propertylist , address , building , alternateurl ,land
WHERE propertylist.propertyid = address.propertyid
AND address.propertyid = building.propertyid
AND building.propertyid = alternateurl.propertyid
AND alternateurl.propertyid = land.propertyid

edited Mar 25 '15 at 03:09

answered Feb 21 '15 at 11:16

shauryachats

9,275
4
34
47

Sir, it took, 41.5744 seconds to count 13684 records... and is there a way to make it quicker – Sahil Feb 21 '15 at 11:22
`COUNT (*)` is one of the [best](http://stackoverflow.com/a/5060385/2842375) ways to count the total number of records. – shauryachats Feb 21 '15 at 11:27
is my query correct? the propertyid is the primary key of every table. is this the right way to fetch data from the database? – Sahil Feb 21 '15 at 11:32
Yes, it is. You could also use `INNER JOIN`. Did the above answer help you? – shauryachats Feb 21 '15 at 11:35
yes and no. i am implementing the paging system in my website and your stated query is taking around 41 seconds to count which is bottleneck as i need the fast count mechanism so that my website can response in appropriate time – Sahil Feb 21 '15 at 11:40
Try using `SELECT DISTINCT COUNT(propertylist)`. – shauryachats Feb 21 '15 at 12:00
Thanks sir. i realized that the problem was related to indexing. that is why it was taking much amount of time. i used your way to count. – Sahil Feb 22 '15 at 03:20
Glad to help. :) How much time does it take now? – shauryachats Feb 22 '15 at 03:43

score 0 · Answer 2 · answered Feb 21 '15 at 11:16

To get the count of your query you shall use count.

So your Query will be

SELECT count (DISTINCT propertylist.propertyid
    ,propertylist.price
    ,propertylist.publicremarks
    ,address.addressline1
    ,address.streetaddress
    ,address.city
    ,address.postalcode
    ,alternateurl.maplink
    ,building.bathroomtotal
    ,building.bedroomtotal
    ,building.constructeddate
    ,building.sizeinterior
    ,building.type
    ,building.basementfeatures
    ,building.basementtype
    ,building.constructionstyleattachment
    ,propertylist.ammenitiesnearby
    ,propertylist.features
    ,propertylist.transactiontype
    ,propertylist.lastupdated
    ,propertylist.communityfeatures
    ,land.acreage )
FROM propertylist
    ,address
    ,building
    ,alternateurl
    ,land
WHERE propertylist.propertyid = address.propertyid
    AND address.propertyid = building.propertyid
    AND building.propertyid = alternateurl.propertyid
    AND alternateurl.propertyid = land.propertyid

In Simple

SELECT COUNT(DISTINCT column_name) FROM table_name;

You shall also refer here

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT propertylist.propertyid ,propertylist.price ,propertylist.publi' at line 1 — Sahil, Feb 21 '15 at 11:24

score 0 · Answer 3 · answered Feb 21 '15 at 12:02

What I did in sqlserver side was give pageno and pagesize as a arguments, then created cte with rowno and while executing got the count of total record.

Also use join with condition as much as possible with to filter data. And also give the always alias of tables to readable and maintainable query.

check this example, just change your column as I written

declare @pageno int , @pagesize int

;With cte
as 
(
SELECT ROW_NUMBER() OVER ( order by propertyid or giveyourColumnidToshort ) as rowID, * FROM (    
    SELECT DISTINCT propertylist.propertyid
        ,propertylist.price
        ,propertylist.publicremarks
        ,address.addressline1
        ,address.streetaddress
        ,address.city
        ,address.postalcode
        ,alternateurl.maplink
        ,building.bathroomtotal
        ,building.bedroomtotal
        ,building.constructeddate
        ,building.sizeinterior
        ,building.type
        ,building.basementfeatures
        ,building.basementtype
        ,building.constructionstyleattachment
        ,propertylist.ammenitiesnearby
        ,propertylist.features
        ,propertylist.transactiontype
        ,propertylist.lastupdated
        ,propertylist.communityfeatures
        ,land.acreage
    FROM propertylist as pl 
        JOIN address as a           on pl.propertyid = a.propertyid 
        join building as b          on a.propertyid = b.propertyid
        join alternateurl as an     on b.propertyid = an.propertyid 
        join land as l              on an.propertyid =  l.propertyid --you have to join more data if you have   

    )   
    tt
)

 SELECT *, noofRows= (SELECT count(propertyid or giveyourColumnidToshort) FROM CTE)          
  FROM CTE WHERE rowID >= @pageno AND ((@pageno  = -1) or rowID<= @pageno)

The OP is using MySql. The t-sql construct you use is not available in mysql. — rene, Feb 21 '15 at 15:25

score 0 · Answer 4 · answered Feb 21 '15 at 12:14

Take a look at SQL_CALC_FOUND_ROWS along with a LIMIT on your query. You'd need a second query to get the total results, but this seems to be the easiest way to handle pagination.

https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

how to count the number of rows fetched from the query consists of data from many tables

4 Answers4