I think I can do that:
I don't have any other indexes, just the keys.
mysql> show create table data__ProcessedDataFrames;
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| data__ProcessedDataFrames | CREATE TABLE `data__ProcessedDataFrames` (
`processed_id` int(10) unsigned NOT NULL auto_increment,
`top_level_product_name` varchar(255) default NULL,
`test_id` int(10) unsigned default NULL,
`payload_time` double default NULL,
`universal_time` double default NULL,
`processed_data` mediumblob,
PRIMARY KEY (`processed_id`),
KEY `test_id` (`test_id`),
KEY `payload_time` (`payload_time`),
KEY `top_level_product_name` (`top_level_product_name`)
) ENGINE=MyISAM AUTO_INCREMENT=1392568 DEFAULT CHARSET=latin1 |
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
As for the amount of data I expect to get .... infinite really.
Our setup: we have serveral boxes we are running tests from, where sql is
the storage engine on each local box, then we will have a main storage
area for all relevant tests. Based on passed data, the the tables size
will be pushing 4G as it is (when we port the data) and expect at least
that much more over the life of this software, but since the payloads I
will be getting the data from have not been developed yet, I can't be
entirely sure. One of the reasons I was inquiring as to whether breaking
it up into several tables would be a good idea is because it would make it
easier for me to merge the data from the different testers into the main
data repository that way. Otherwise I will have to figure out a good way
of redoing the test_id in each test that is stored in the main repository.
Slow queries will be a little hard to show without giving a full
evaluation of my system. So I'll simplify it a little. I'm doing several
joins to get the right parameters to query this table in a stored
procedure ..... but when it comes down to it, the query on this table is
the big one and I can modify my other joins, just making the query on this
table fast is my concern. Example query:
select payload_time,HEX(processed_data) from data__ProcessedDataFrames
where test_id=18 AND top_level_product_name="DataProduct" AND payload_time
> 118080000.74704 AND payload_time < 1180564096.24967;
What I'm concerned about is with how much data I will eventually have,
even scanning over the KEYS will take a long time.
Thanks
Dave G.
BTW: heres the giant query that I use.
SELECT
E.product_id,
product_name,
D.top_level_product_name,
processed_id,
product_offset,
product_length,
version_id,
byte_order,
ROUND(R.payload_time,6) as payload_time,
SUBSTR(
BINARY(processed_data),
FLOOR(product_offset/8)+1,
CEIL(product_length/8)) as substring,
(SELECT HEX(substring)) as raw_data,
(SELECT toString (
substring,
round(char_length(raw_data)/2,0),
data_type,
(SELECT attribute_value FROM
config__DataProductAttributes WHERE attribute_name =
'FormatString' AND
config__DataProductAttributes.product_id =
E.product_id),
product_offset % 8,
(product_length + (product_offset % 8)) % 8,
product_length,
byte_order,
(SELECT attribute_value FROM config__DataProductAttributes WHERE
attribute_name = 'ConvParams' AND
config__DataProductAttributes.product_id =
E.product_id))) as converted_data,
(SELECT enum_name FROM config__DataProductEnumConversions WHERE
product_id = E.product_id AND
enum_value = converted_data) as enumerated_data,
(SELECT metricTest(converted_data,
(SELECT xmlTestMetric FROM test__TestMetrics
WHERE product_id = E.product_id))) as test_metric,
data_type,
R.test_id
FROM display__DataProducts_in_Element AS E INNER JOIN
(config__DataProducts AS D INNER JOIN
(data__ProcessedDataFrames AS R INNER JOIN
(SELECT
E.element_id,R.test_id,R.top_level_product_name,max(round(payload_time,6))
as payload_time FROM
display__DataProducts_in_Element E,
config__DataProducts D,
data__ProcessedDataFrames R WHERE
payload_time <= ptime AND test_id like str_tid AND
D.top_level_product_name =
R.top_level_product_name AND
E.product_id = D.product_id AND
E.element_id=id GROUP BY
D.top_level_product_name) AS S
ON R.test_id = S.test_id AND R.top_level_product_name =
S.top_level_product_name AND R.payload_time = S.payload_time)
ON D.top_level_product_name = R.top_level_product_name)
ON E.product_id = D.product_id
WHERE E.element_id=id
GROUP BY product_id;
toString is a UDF I wrote for data conversions. the config__ tables are
used to interpret the blob in the data__ProcessedDataFrames table
Thanks again for the help.
> Relevant data might include the actual schema for your tables, (DESC
> does not inform us about what indexes you have or what table types you
> are using) and samples of the queries which are running slowly. Also
> any estimates you may have worked up about how much data you expect
> this thing to hold would also be useful.
>
> - michael dykman
>
>
> On 6/27/07, Dave G <[EMAIL PROTECTED]> wrote:
>> I have a table in my database (currently) that grows to be huge (and I
>> need to keep the data). I'm in a redesign phase and I'm trying to do it
>> right. So here are the relevant details:
>>
>> The table has several keys involved:
>>
>> mysql> desc data__ProcessedDataFrames;
>> +------------------------+------------------+------+-----+---------+----------------+
>> | Field | Type | Null | Key | Default |
>> Extra
>> |
>> +------------------------+------------------+------+-----+---------+----------------+
>> | processed_id | int(10) unsigned | NO | PRI | NULL |
>> auto_increment |
>> | top_level_product_name | varchar(255) | YES | MUL | NULL |
>> |
>> | test_id | int(10) unsigned | YES | MUL | NULL |
>> |
>> | payload_time | double | YES | MUL | NULL |
>> |
>> | processed_data | mediumblob | YES | | NULL |
>> |
>> +------------------------+------------------+------+-----+---------+----------------+
>> 6 rows in set (0.00 sec)
>>
>> This is the table that contains the data I'm interested in currently.
>> Queries on this table when it gets large is slow as molasses. I'm
>> thinking about making a new table for anything with a different test_id
>> .... any opinions as to whether this is good or bad?
>>
>> Before you make fun of me for my questions, I a bit new to database
>> programming.
>>
>> If it is better design to break it into smaller tables (for speed
>> anyway)
>> then I would need to know how to query over multiple tables as though it
>> was one table. Join will do this, but that takes forever (unless of
>> course I may be doing this wrong), so that's not a good option. I need
>> to
>> be able to query over mutiple test_ids, which will be multiple tables,
>> for
>> specific top_level_product_name, with in some time range (using p_time).
>>
>> Any help would be appreciated. I will happily give more information if
>> you need to offer an educated opinion.
>>
>> Thanks
>>
>> David Godsey
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>
>
> --
> - michael dykman
> - [EMAIL PROTECTED]
>
> - All models are wrong. Some models are useful.
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
