Hello,
I am using Delphi XE4 and facing a problem with FireDac (8.0.5) and MySQL.
I'm trying to get the last 100 records from a large table (20 million rows).
When I run the following SQL: {code}select * from largetable order by FId limit 100{code}
in MySQL Workbench it returns in 0.2 seconds.
When I run the above SQL in Delphi through an ADQuery it never returns (Delphi freezes).
Any Ideas ?

Thank you

Edited by: Paschalis Tsepelidis on Jan 21, 2015 5:55 AM

You might want to evaluate how MySQL Workbench is getting those last 100 
records. My hunch is it is doing some of trickery like adding an order 
by descending, then just fetching the first 100 records - that would be 
fast. A query of any sort in Delphi will not do this sort of trickery 
automatically. If you have 20 million rows and want that last 100, the 
first 19,900,900 will need be fetched first and that will certainly 
never be fast.


Paschalis Tsepelidis wrote:
> Hello,
> I am using Delphi XE4 and facing a problem with FireDac (8.0.5) and MySQL.
> I'm trying to get the last 100 records from a large table (20 million rows).
> When I run the following SQL: {code}select * from largetable order by FId limit 100{code}
> in MySQL Workbench it returns in 0.2 seconds.
> When I run the above SQL in Delphi through an ADQuery it never returns (Delphi freezes).
> Any Ideas ?
>
> Thank you
>
> Edited by: Paschalis Tsepelidis on Jan 21, 2015 5:55 AM
>