Sorry for bothering everybody, but lots of things and wanted to share the results.
The results for several queries are as follows:
SQL string: Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz' order by TERM,TO_LANG
Execquery: 2 Rowcount: 43637 Rows: 2281
SQL string: Select * from DEFINITIONS where FROM_LANG=1 and TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz'
Execquery: 16 Rowcount: 43119 Rows: 2281
SQL string: Select * from DEFINITIONS where TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz'
Execquery: 47 Rowcount: 479 Rows: 2454
SQL string: Select * from DEFINITIONS where FROM_LANG=1
Execquery: 2 Rowcount: 48931 Rows: 1438178
SQL string: Select DEFINITION from DEFINITIONS where FROM_LANG=1
Execquery: 88 Rowcount: 44927 Rows: 1438178
SQL string: Select DEFINITION from DEFINITIONS where TERM='get'
Execquery: 90 Rowcount: 98 Rows: 12
SQL string: Select * from DEFINITIONS where TERM >= 'get' and TERM <= 'getzzzzzzzzzzzzzzzzzzzzz' order by TERM,TO_LANG
Execquery: 1 Rowcount: 567 Rows: 2454
We can see that, when FROM_LANG is included in the query, getting row count takes a long time.
In order to find out whether this is because it returned a large data set or because of any other reason, I tried the followings:
1. I have recreated the database: Same results
2. I have changed the data type of FROM_LANG to text: Same results
3. I have changed the data for FROM_LANG in the database from "1" to "ABC" and run the query as FROM_LANG='ABC': Same results
4. I have recreated the database (FROM_LANG data type is integer) and run the following queries:
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500
Execquery: 4 Rowcount: 24 Rows: 500
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,5000
Execquery: 1 Rowcount: 213 Rows: 5000
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,50000
Execquery: 1 Rowcount: 1222 Rows: 50000
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500000
Execquery: 1 Rowcount: 17962 Rows: 500000
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,5000000
Execquery: 3 Rowcount: 53188 Rows: 1438177
Now We are sure that, the time takes to execute RowCount directly proportional to the number of records returned.
RESULT
======
If you want to process huge amount of data, use LIMIT option for SELECT statement and get data by chunks. See the results given below.
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 1,500
Execquery: 0 Rowcount: 20 Rows: 500
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 501,5000
Execquery: 1 Rowcount: 286 Rows: 5000
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 5001,50000
Execquery: 1 Rowcount: 1506 Rows: 50000
SQL string: Select * from DEFINITIONS where FROM_LANG=1 limit 50001,500000
Execquery: 2 Rowcount: 19392 Rows: 500000
Thanks for everybody.
Regards