I'm wondering if these numbers look typical of h2, or if I still have some
bottleneck I haven't identified.
I have a probes table (mentioned in other threads) with one index over two
columns: eid (int) and name (varchar). (eid, name) is unique. For each eid
there might be 10k-500k rows.
The table has about 38M rows. A query that retrieves 500 rows matching a
single eid with 240k rows, takes about 5 seconds.
This is using the TABLE() inner join, instead of a big IN query. The
explain analyze output shows it hitting the index.
If I close the db and re-open it, the same query takes much less than a
second, which I suspect reflects the OS block cache. Though, as I mentioned
earlier, the disk doesn't seem very strained during the first query. I'm
not sure what that means.
The query:
SELECT `probes`.`name` as `gene`, `probes`.`id` FROM `probes` INNER
JOIN TABLE(name varchar=?) T ON T.`name`=`probes`.`name` WHERE
(`probes`.`eid` = ?)
The explain analyze:
SELECT
PROBES.NAME AS GENE,
PROBES.ID
FROM TABLE(NAME VARCHAR=?1) T
/* function */
/* scanCount: 501 */
INNER JOIN PUBLIC.PROBES
/* PUBLIC.EID_NAME_INDEX_8: EID = ?2
AND NAME = T.NAME
*/
ON 1=1
/* scanCount: 1000 */
WHERE (PROBES.EID = ?2)
AND (T.NAME = PROBES.NAME)
--
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/groups/opt_out.
