Search code examples
mysqldbeaver

mySQL select statement only returning data from a certain key range

I'm running a select statement on a table to return all rows where the value in column "NewickSpeciesName" is 'Acanthisitta_chloris':

SELECT * from PfamAlignments
WHERE 
NewickSpeciesName ='Acanthisitta_chloris'
INTO OUTFILE '/home/raymondhon/public/pfamstest.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This returns a csv with some but not all of the rows. Looking at the .cvs, it looks like mysql pulled rows fulfilling my criteria, but only up to a certain value on my key column. That is, in the table, there are rows where "NewickSpeciesName" is "Acanthisitta_chloris" and the key column value is in the multiples of millions. (https://i.sstatic.net/hGCen.png)

Whereas, in the .csv, the the largest value in the key column is 322079. (https://i.sstatic.net/3xJPP.png)

What might be the cause for this? If it makes any difference, my table has over 11 million rows, and I'm running the mysql script on DBeaver, and I'm SSH tunneling from my local computer to the database I'm accessing.

Solution

  • DBeaver converts your SQL statement and appends a LIMIT clause. To avoid this, set ResultSet Fetch size in DBeaver settings to -1 (=infinite).