Lets take the example of the sakila database.
In that you want to load data into payment table every day. In the
optimization suggested by Chandru, we need to load the data into a
intermediate table first then onto the mail table.
Now to create the intermediate table either you can use a load data infile
or generate insert statements and load the data. If you have csv engine in
the mysql server another approach would be as follows :
create table intermediate as select * from payment limit 0;
alter table intermediate engine=csv;
Now at the command prompt under the data directory of the database delete
intermediate.CSV. Then mv your dumpfile to intermediate.CSV.
The dumpfile should be of the following format :
"16049","599","2","15725","2.99","2005-08-23 11:25:00","2006-02-15 22:24:13"
Now you don't have to load the dumpfile into the mysql server to create a
table.
HTH,
On Mon, May 26, 2008 at 4:04 PM, Ananda Kumar <anandkl@stripped> wrote:
> Hi Alex,
> Yes, CSV engine is enabled.
>
> Engine | Support |
> Comment |
>
>
> +------------+----------+----------------------------------------------------------------+
> | MyISAM | YES | Default engine as of MySQL 3.23 with great
> performance |
> | MEMORY | YES | Hash based, stored in memory, useful for
> temporary
> tables |
> | InnoDB | DEFAULT | Supports transactions, row-level locking, and
> foreign keys |
> | BerkeleyDB | NO | Supports transactions and page-level
> locking |
> | BLACKHOLE | YES | /dev/null storage engine (anything you write to
> it
> disappears) |
> | EXAMPLE | YES | Example storage
> engine |
> | ARCHIVE | YES | Archive storage
> engine |
> | CSV | YES | CSV storage
> engine |
> | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based
> tables |
> | FEDERATED | YES | Federated MySQL storage
> engine |
> | MRG_MYISAM | YES | Collection of identical MyISAM
> tables |
> | ISAM | NO | Obsolete storage engine
>
> so, u mean to say i create the csv file and move it into my data directory
> and do the data load from there. How does this help optimization.
>
> regards
> anandkl
>
>
> On 5/26/08, Alex Arul Lurthu <alexj@stripped> wrote:
> >
> > Even before looking into compiling check if you have CSV enabled already
> > using :
> >
> > mysql> show engines;
> >
> >
>
> +------------+---------+----------------------------------------------------------------+
> > | Engine | Support |
> > Comment |
> >
> >
>
> +------------+---------+----------------------------------------------------------------+
> > | MyISAM | YES | Default engine as of MySQL 3.23 with great
> > performance |
> > | MEMORY | YES | Hash based, stored in memory, useful for
> temporary
> > tables |
> > | InnoDB | DEFAULT | Supports transactions, row-level locking, and
> > foreign keys |
> > | BerkeleyDB | NO | Supports transactions and page-level
> > locking |
> > | BLACKHOLE | YES | /dev/null storage engine (anything you write to
> it
> > disappears) |
> > | EXAMPLE | YES | Example storage
> > engine |
> > | ARCHIVE | YES | Archive storage
> > engine |
> > | CSV | YES | CSV storage
> > engine |
> > | ndbcluster | NO | Clustered, fault-tolerant, memory-based
> > tables |
> > | FEDERATED | YES | Federated MySQL storage
> > engine |
> > | MRG_MYISAM | YES | Collection of identical MyISAM
> > tables |
> > | ISAM | NO | Obsolete storage
> > engine |
> >
> >
>
> +------------+---------+----------------------------------------------------------------+
> > 12 rows in set (0.00 sec)
> >
> > If not then you might have to compile the mysql server with the
> –with-*csv
> > *-storage-engine from the source.
> >
> >
> > On Mon, May 26, 2008 at 3:41 PM, Ananda Kumar <anandkl@stripped> wrote:
> >
> >> Hi Alex,
> >> How do i get a csv engine into mysql binaries. Also the file is not
> comma
> >> seperate values files, the value is seperated by a different a different
> >> value.
> >>
> >> regards
> >> anandkl
> >>
> >>
> >> On 5/26/08, Alex Arul Lurthu <alexj@stripped> wrote:
> >> >
> >>
> >> > On Mon, May 26, 2008 at 2:40 PM, chandru <
> pradeep.chandru@stripped
> >> >
> >> > wrote:
> >> >
> >> >> Hi anand,
> >> >> To add to alex's views, I see that you do two major operations
> >> >> 1. Insert
> >> >>
> >> >> * Use a temporary table that loads the data initially and then
> you
> >> >> copy the same to the original table, which can prevent load on
> the
> >> >> original table and shall be faster as well.
> >> >>
> >> >
> >> > Its a good point that Chandru is making here. One more optimization to
> >> go
> >> > with this is the following :
> >> >
> >> > If you have csv engine compiled into your binaries, then the
> temporary
> >> > table can created just by preparing the file with comma separated
> values
> >> and
> >> > moving the file to data directory and then start the load from there.
> >> >
> >> >
> >> > --
> >> > Thanks
> >> > Alex
> >> > http://alexlurthu.wordpress.com
> >> >
> >>
> >>
> >
> >
> >
> > --
> > Thanks
> > Alex
> > http://alexlurthu.wordpress.com
> >
>
--
Thanks
Alex
http://alexlurthu.wordpress.com
