Hi Thomas, Sure, I have indices, you can see that in explain analyze output above, however it takes too much time to run the query when I use "between" condition
select c.*,r.* from requests r join clients c on r.cl_id = c.cl_id where r.req_id between 1L and 2L although select c.*,r.* from requests r join clients c on r.cl_id = c.cl_id where r.req_id = 1L takes 5ms only on database with nearly 8M records. On Monday, April 27, 2015 at 4:39:12 PM UTC+3, Thomas Mueller wrote: > > Hi, > > Do you have any indexes? Did you read > http://h2database.com/html/performance.html ? > > Regards, > Thomas > > > On Sun, Apr 26, 2015 at 6:52 PM, sim <sim...@mail.ru <javascript:>> wrote: > >> Hi, >> >> Let's assume I have two tables >> >> table clients >> ============= >> cl_id bigint primary key, >> cl_name varchar >> >> table requests >> ============== >> req_id identity primary key, >> req_type integer, >> req_date timestamp, >> cl_id bigint foreign key >> >> And I want to run a request like >> >> select c.*,r.* >> from requests r >> join clients c on r.cl_id = c.cl_id >> where r.req_id between 1L and 2L >> >> It takes ~40s to get 2 rows >> >> explain analyze: >> >> SELECT >> C.CL_ID, >> C.CL_NAME, >> R.REQ_ID, >> R.REQ_TYPE, >> R.REQ_DATE, >> R.CL_ID >> FROM PUBLIC.CLIENTS C >> /* PUBLIC.CLIENTS.tableScan */ >> /* scanCount: 11 */ >> INNER JOIN PUBLIC.REQUESTS R >> /* PUBLIC.REQUESTS_CLID_IDX: CL_ID = C.CL_ID */ >> ON 1=1 >> /* scanCount: 7528481 */ >> WHERE (R.CL_ID = C.CL_ID) >> AND ((R.REQ_ID >= 1) >> AND (R.REQ_ID <= 2)) >> /* >> reads: 3439464 >> */ >> >> >> Not helps much a request like >> select c.*,r.* >> from (select * from requests where req_id between 1L and 2L) r >> join clients c on r.cl_id = c.cl_id >> >> explain analyze: >> >> SELECT >> C.CL_ID, >> C.CL_NAME, >> R.REQ_ID, >> R.REQ_TYPE, >> R.REQ_DATE, >> R.CL_ID >> FROM PUBLIC.CLIENTS C >> /* PUBLIC.CLIENTS.tableScan */ >> /* scanCount: 11 */ >> INNER JOIN ( >> SELECT >> REQUESTS.REQ_ID, >> REQUESTS.REQ_TYPE, >> REQUESTS.REQ_DATE, >> REQUESTS.CL_ID >> FROM PUBLIC.REQUESTS >> /* PUBLIC.PRIMARY_KEY_F: REQ_ID >= 1 >> AND REQ_ID <= 2 >> */ >> WHERE (REQ_ID >= 1) >> AND (REQ_ID <= 2) >> ) R >> /* SELECT >> REQUESTS.REQ_ID, >> REQUESTS.REQ_TYPE, >> REQUESTS.REQ_DATE, >> REQUESTS.CL_ID >> FROM PUBLIC.REQUESTS >> /++ PUBLIC.REQUESTS_CLID_IDX: CL_ID IS ?1 ++/ >> /++ scanCount: 1 ++/ >> WHERE (REQUESTS.CL_ID IS ?1) >> AND ((REQ_ID >= 1) >> AND (REQ_ID <= 2)): CL_ID = C.CL_ID >> */ >> ON 1=1 >> /* scanCount: 12 */ >> WHERE R.CL_ID = C.CL_ID >> /* >> reads: 3439464 >> */ >> >> Is it ok such a simple request takes so much time? >> >> -- >> 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...@googlegroups.com <javascript:>. >> To post to this group, send email to h2-da...@googlegroups.com >> <javascript:>. >> Visit this group at http://groups.google.com/group/h2-database. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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 http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
