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