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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
