Hi, consider this time series table:
CREATE CACHED TABLE item_value
(
item_id SMALLINT NOT NULL,
created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
value DOUBLE NOT NULL,
PRIMARY KEY (custom_item_id, created)
);
Added 12 million rows, the table takes 1500mb, index takes 900 mb.
Range queries on the primary key are often slow (up to 60 seconds):
SELECT * FROM item_value WHERE item_id = ? AND created BETWEEN ? AND ?
ORDER BY custom_item_id, created
Some questions:
-Any way to reduce the table and index size? 90% overhead = lots of I/
O
-Does table fragmentation affect query speed? Can tables be
defragmented?
-Other hints to improve execution speed (e.g. "partitioning" by
item_id)?
Thanks,
Peter
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
