Hi,
Actually my table is already built around a sorted column.
This is a naive question but is it a difference between postgreSQL
and MySQL ? (see this page)
http://www.postgresql.org/docs/current/interactive/queries-limit.html
"When using LIMIT, it is important to use an ORDER BY clause that
constrains the result rows into a unique order. Otherwise you will
get an unpredictable subset of the query's rows. You might be asking
for the tenth through twentieth rows, but tenth through twentieth in
what ordering? The ordering is unknown, unless you specified ORDER BY. "
Thanks for your help
Olivier
Le 11 juil. 08 à 20:29, Richard Holland a écrit :
I'm guessing that you're seeing duplicate results in your output, or
some that are completely missing, or a mixture of both?
Both .... because the results are not sorted
Le 12 juil. 08 à 01:15, Syed Haider a écrit :
Hi Olivier,
If I understand you correctly, and you want to have ORDER BY clause in
biomart-perl query builder, I would suggest to build your table around
the required ORDER BY columns. If you execute ORDER BY on the fly (via
biomart-perl), this will be done with each batch of the query and
similarly every time a user/new user asks for results with different
attributes/filters. This would make your query response very slow. If
you know the bottle neck which i guess is the case, better fix it
on the
table level.
regards
syed
On Fri, 2008-07-11 at 17:22 +0200, Olivier Arnaiz wrote:
Hello,
I am using biomart with a postgres database (7.4).
And all seem work perfectly with the count of the results but I can
not obtain a good table with an 'export' or a 'view all results'.
When I see the log4 log, I can remark that the query is split in
many sql
using LIMIT and OFFSET... So why not ...
but I do not see an ORDER BY command and this parameter seems to be
necessary to obtain my good results...
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t LIMIT 200
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t LIMIT 200 OFFSET 200
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t LIMIT 400 OFFSET 400
instead of :
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t ORDER BY main.key LIMIT 200
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t ORDER BY main.key LIMIT 200 OFFSET
200
SELECT main.name,[dm_table].field FROM main, dm WHERE
main.key=dm.key AND dm.field =t ORDER BY main.key LIMIT 400 OFFSET
400
That is a problem with postgres versus MySQL (I do not have
any experience with MySQL) or a problem with my config ... or
something else ?
Any suggestions
Thanks
Olivier
--
Olivier Arnaiz
CGM-CNRS
91198 Gif-sur-Yvette
01 69 82 43 75
ParameciumDB:
http://paramecium.cgm.cnrs-gif.fr/
--
======================================
Syed Haider.
EMBL-European Bioinformatics Institute
Wellcome Trust Genome Campus, Hinxton,
Cambridge CB10 1SD, UK.
======================================
--
Olivier Arnaiz
CGM-CNRS
91198 Gif-sur-Yvette
01 69 82 43 75
ParameciumDB:
http://paramecium.cgm.cnrs-gif.fr/
