optimization question

[siliconmike]

I've got a table T with 20 columns (fixed length) and one column C
which is varchar.

Somehow C has to be varchar.

T may have millions of records.

Is there a way to optimize this situation ?

Any ideas?

[Bill Karwin]

siliconmike wrote:

I've got a table T with 20 columns (fixed length) and one column C
which is varchar.

Somehow C has to be varchar.

T may have millions of records.

Is there a way to optimize this situation ?

Optimize for what task or goal? Space efficiency? Import speed? Query
speed? If query speed, what kind of queries do you need to make against
the data?

It sounds to me like someone gave you an order like, "store all these
millions of text strings in a database, and make sure it's *optimized*."
Only they didn't tell you the the criteria I ask for above.

Regards,
Bill K.

[siliconmike]

Optimize for what task or goal? Space efficiency? Import speed? Query speed? If query speed, what kind of queries do you need to make against the data?
Import speed, but without making C fixed length. For example, would
taking C to a different table help?
It sounds to me like someone gave you an order like, "store all these millions of text strings in a database, and make sure it's *optimized*." Only they didn't tell you the the criteria I ask for above.

Sounds wrong.

[Bill Karwin]

siliconmike wrote:

Import speed, but without making C fixed length. For example, would
taking C to a different table help?

I'd recommend using the LOAD DATA INFILE statement. The docs suggest it
can up to 20 times faster than inserting using INSERT.

LOAD DATA INFILE uses field delimiters; it does not require the input
data formatted in fixed length. See examples at
http://dev.mysql.com/doc/mysql/en/load-data.html

Also read the suggestions for speeding up inserts and LOAD DATA on this
page: http://dev.mysql.com/doc/mysql/en/insert-speed.html
Disabling the keys during import will probably help a lot.

Regards,
Bill K.

[siliconmike]

Bill Karwin wrote:

siliconmike wrote:

Import speed, but without making C fixed length. For example, would
taking C to a different table help?
I'd recommend using the LOAD DATA INFILE statement. The docs suggest

it can up to 20 times faster than inserting using INSERT.

Also query speeds..
Queries like:

select * from T where PRIMARY_KEY=45

It is only column C (varchar) that forces the whole table to be dynamic
format.
If I make C fixed length, it would waste lot of space.

I presume here that queries on fixed length format tables would execute
much faster than variable length ones.

Maybe I can remove column C from T and write the data intended for C in
separate files named by the PRIMARY_KEY.

Or other ideas ?

[Bill Karwin]

siliconmike wrote:

I presume here that queries on fixed length format tables would execute
much faster than variable length ones.

Not always. There is reportedly a tradeoff between fixed-length of CHAR
and simply the lesser average size of VARCHAR. Variable length fields
mean more data fits into the same amount of RAM. So it depends on the
physical characteristics of a given dataset.

To be sure, you should try some tests using both CHAR and VARCHAR for a
representative dataset, and run some timings (avoid presuming if you can
actually measure!).

http://dev.mysql.com/doc/mysql/en/cu...enchmarks.html

Regards,
Bill K.