A workaround is to use mysqlhotcopy to snapshot the table and also only copy
the header to the MYI file. Then delete the original and rename the copy back
to the original. This will effectively drop all indexes and should take no
more time than what the disk takes to copy the .my* files.
Could you be more specific? What is SOURCE? Where do I use that? I tried
to search, but I find a lot of hits related to source code.
-Original Message-
From: Ligaya A. Turmelle [mailto:[EMAIL PROTECTED]
Sent: Monday, October 09, 2006 6:57 PM
To: Daevid Vincent; mysql@lists.mysql.com
You'll probably need to create a database first (to host the table).
This can be done most easily from the GUI MySQL Administrator tool, or
from phpMyAdmin if you have it, or from a mysql command line:
http://dev.mysql.com/doc/refman/5.0/en/create-database.html
To create a table use the
Read the section on the manual on the Client and Utility programs,
specifically mysql. The syntax is essentially the same as you have in
your code something like this from the command line
mysql -u dbUser -pMyPassword dbName SomeFileWithLotsOfSQLCommands.sql
Note there is no space
Hi list subscribers,
i am inserting millions of product rows from csv files via LOAD DATA INFILE.
Every product has a price, but this price-strings vary heavily.
The main difference between them, is the decimal format. Sometimes it is
european like this:
1.000.000,00
sometimes its american
Benjamin, any chance you can pre-process the data with an external
script prior to loading into your database?
Doing this sort of manipulation in SQL may be possible, but it surely
will be tricky. RegEx support in MySQL is present, but in my
(limited) experience with it, it's really only good
Chris, Please read this in its entirety !!!
I learned why 2 years ago while using MySQL 4.1 for Windows
and looking at the folder which contains the .MYDs and .MYIs
while watching 'ALTER TABLE ... DROp INDEX' in action:
If a table T is a MySQL table having four indexes (ndx1,ndx2,ndx3,ndx4)
and
Hi,
I am using mySQL 5.0 and I have 2 tables with few hundred millions of
records.
To optimize things, I am using MyISAM tables, using the smallest possible data
type and have set indexes.
Now, the problem which I am facing is that mySql process is wasting lot of
time in disk access the CPU
I'd like some advice on setting up databases that contain entries for
the same item in more than one language. For instance, here's what I
currently do for a table that contains the same topics translated into
English and Arabic:
CREATE TABLE `TOPIC` (
`TopicID` int(11) NOT NULL auto_increment,
Hi,
MySQL 5.0.26, a new version of the popular Open Source Database
Management System, has been released.
This is part 2 of the related announcement, listing the changes of
5.0.25 over 5.0.24a which was the last published release of 5.0.
As 5.0.25 was not generally released, most users will
I am going to upgrade my database from version 4 to 5.
However I found some of my web application doesn't work on MySQL5
e.g following statement works in 4 but not 5
SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
I had this problem here and i change my query to:
SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
Hi,
MySQL 5.0.26, a new version of the popular Open Source Database
Management System, has been released. The Community Edition is now
available in source and binary form for a number of platforms from our
download pages at
http://dev.mysql.com/downloads/ and mirror sites.
Note that not
MySQL 5 has a CSV storage engine
You can read Page 639 of the MySQL Administrator's Guide and Language Reference
(2nd edition)
ISBN 0-672-328700-4
Try this:
Step 1:
CREATE TABLE NewDataCSV (firstname varchar(30),lastname varchar(30))
Engine=CSV;
This should create NewDataCSV.frm and
Here is my suggestion, but like every other thing I post here I urge you to
take it with a grain of salt. Set up the following tables (described in
rough terms, not in SQL):
topic_index
topic_id autonumber
... Whatever else you need to keep track of that identifies a topic
Jason
following statement works in 4 but not 5
SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as
cat_state, c.name as cat_name, c.description as cat_desc,
c.image, c.url, m.member_name as mod_name, m.member_id as
mod_id, m.is_group, m.group_id, m.group_name, m.mid
I want to write a sp return paging of recordset.
CREATE PROCEDURE `sp_GetJobsDetails`(Page INT, PageSize INT)
BEGIN
DECLARE RecordBegin INT;
DECLARE tmpPageSize INT;
SET RecordBegin = Page * PageSize - PageSize;
SET tmpPageSize = PageSize + 1;
SELECT JOB_ID
I think what is strange to me is that InnoDB is locking on the subquery
table at all. Here's another example:
DELETE
FROM Vers
WHERE (
Vers.elementID IN (
SELECT Elems.ID
FROM Elems
WHERE (Elems.nodeID = ?)))
We'll do some testing with innodb_locks_unsafe_for_binlog but if this
fixes the problem then it is a pretty safe assumption that the problem
also exists with subqueries in DELETE and UPDATE and not just for that
one case of INSERT as the article points out.
-Original Message-
From: Baron
I'm running MySQL 5.0.15 on Windows system.
How do I count how many specific char is there in a column, for example
finding 'c' in lowercase string of Characteristics would total to 3.
--
`Twas brillig, and the slithy toves
On Fri, 01 Sep 2006 16:35:08 -0500, [EMAIL PROTECTED] wrote:
At 02:59 PM 9/1/2006, you wrote:
mos wrote:
AMD Athlon 64X2 3800+ Dual Core S939 Manchester (2x512K cache)
AMD Athlon 64X2 4200+ Dual Core S939 Manchester (2x512k cache)
AMD Athlon 64X2 4400+ Dual Core S939 Toledo (2x1MB cache)
AMD
Yup, innodb_locks_unsafe_for_binlog=1 fixes the problem and so does your
suggestion of using a JOIN instead of a subselect.
-Original Message-
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 8:54 AM
To: Baron Schwartz; Rick James
Cc:
It's not a bug in InnoDB. There are far more knowledgeable people than I on this list,
but it should get a share-mode lock on anything it selects from, otherwise there might
be inconsistencies as it tries to serialize different transactions into the binary log
for replication. If the
Then I guess I am not understanding why re-writing the statement as a
JOIN alleviates that need.
-Original Message-
From: Baron Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:35 AM
To: Robert DiFalco
Cc: Rick James; mysql@lists.mysql.com; [EMAIL PROTECTED]
It probably uses a single lock to handle a JOIN, and two locks to handle a
sub-SELECT. I doubt that it helps, but if I'm right it will change what you
see when you poking around.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX:
Right, as I understand it the query optimizer in 5.2 will simply rewrite
these sub selects as joins when possible.
-Original Message-
From: Jerry Schwartz [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:45 AM
To: Robert DiFalco; 'Baron Schwartz'
Cc: 'Rick James';
Hi All,
I have a question on purging some old bin-logs.
whats the best way to do it?
This is a fairly old version - 4.0.18-standard-log. I have 128 1 GB
files out there, going back 8 months.
I think the correct syntax is :
PURGE BINARY LOGS TO 'mysql-bin.010';
but from what the
Hello
Besides some obvious differences in implementations between util.Date
and sql.Date are there any other issues/advantages or disadvantages
using one versus the other?
Thanks
Oops, I should have emphasized that the TEMPORARY TABLE should be MyISAM or
Memory so as to avoid locking on it. (This _assumes_ that it is ok to split
the SELECT and DELETE into separate transactions. Often the semantics of
such a move allow such. YMMV)
-Original Message-
From:
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
DÆVID
-Original Message-
From: George Law [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 10, 2006 11:56 AM
To: mysql@lists.mysql.com
Subject: purging bin logs
Hi All,
I have a question on purging some old
A java.sql.Date does not have time information - just the day-month-year.
A java.util.Date has date and time information.
If you need date and time, use java.sql.Timestamp.
It's not very pretty moving from one to the other.
David
-Original Message-
From: Feliks Shvartsburd
Hi list,
I have three tables:
product (ID, name)
product2attribute (ID, product, attribute)
attribute (ID, name)
Product - Attribute is an n:m relation, so one product can have two or
more attributes and of course there can be many products with an attribute.
I want to select alle products
Surendra Singhi wrote:
Hi,
I am using mySQL 5.0 and I have 2 tables with few hundred millions of
records.
To optimize things, I am using MyISAM tables, using the smallest possible data
type and have set indexes.
Now, the problem which I am facing is that mySql process is wasting lot of
1) create table T1 like T;
This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4.
2) alter table T1 drop index ndx3;
This drops index ndx3 on the empty T1, which should be instantaneous.
3) insert into T1 select * from T;
This will populate table T and load all three(3) indexes for
Hi,
MySQL dosen't have built-in function for counting substring. But we can
create user-defined functions for this.
Like,
CREATE FUNCTION substrCount(x varchar(255), delim varchar(12)) returns int
return (length(x)-length(REPLACE(x, delim, '')))/length(delim);
Then try,
SELECT
35 matches
Mail list logo
