In HSQLDB, I am used to writing queries like: "SELECT TOP 10 * FROM article ORDER BY id DESC"
Meaning: get the 10 newest article records from table "article" (in this case 500 000 rows). The "id" column is naturally the primary key and everything happens in milliseconds. Well, I just discovered to my amazement that I can't do that in H2... well, I can, but the same query ran 200 seconds. I'm absolutely certain that there's a simple way to write this efficiently in H2, but would this be considered a bug or not? The execution plan for the query in my case is: explain plan for select top 10 * from article order by id desc; PLAN <http://127.0.0.1:8082/query.do?jsessionid=1e15498c32d610fe9786b6a7986a555d#> SELECT ARTICLE.ID, ARTICLE.CODE, ARTICLE.NAME, ARTICLE.UNIT, ARTICLE.ADDRESS, ARTICLE.SECTORGROUP_ID, ARTICLE.UNLOCK, ARTICLE.WEIGHT, ARTICLE.RVSALDO, ARTICLE.PRODUCT_GROUP, ARTICLE.SUPPLIERS_CODE, ARTICLE.PURCHASE_PRICE, ARTICLE.OPT1, ARTICLE.OPT2, ARTICLE.OPT3, ARTICLE.OPT4, ARTICLE.OPT5, ARTICLE.OPT6, ARTICLE.OPT7, ARTICLE.OPT8, ARTICLE.CATEGORY, ARTICLE.AUXCODE FROM PUBLIC.ARTICLE /* PUBLIC.ARTICLE.tableScan */ ORDER BY 1 DESC LIMIT 10 I would appreciate any feedback from more experienced folks. :) I've only worked with H2 for a year or so, and I've started to prefer it to other tools (MySQL, HSQL)... but occasionally I still find something surprising. :) -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
