Re: a strange problem
liaojian_163 schrieb: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | [...] 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | [...] mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Weird result on max compared to order by
[EMAIL PROTECTED] schrieb: Hi, I did a select on a primary key.. Select max(account_id) from mytable; -- it gave me a value X I did a select with order by Select account_id from mytable order by account_id desc limit 3 -- it gave me a value of Y ( Y is the right value ) I was wondering why it didn't gave me the same value and after some time doing a select max gave me the right value Y seems your index was corrupted -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items
the tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
mysql SELECT DISTINCT items.title from items inner join taggings on
(items.id = taggings.item_id) inner join tags on (tags.id =
taggings.tag_id) WHERE tags.name IN ('red', 'blue');
+---+
| title |
+---+
| tagged_red|
| tagged_red_and_blue |
| tagged_red_and_green |
+---+
Do you have an idea how to create an AND query?
Ingo
On Apr 22, 2008, at 5:42 PM, Sebastian Mendel wrote:
Ingo Weiss schrieb:
Hi all,
I have an application where items can be tagged. There are three
tables
'items', 'taggings' and 'tags' joined together like this:
items inner join taggings on (items.id = taggings.item_id) inner join
tags on (tags.id = taggings.tag_id)
Now I have been struggling for some time now with coming up with
the SQL
to find the items the tags of which include a specified list of tag
names. Example:
I am looking for items tagged with 'blue' and 'red'. This should
find me:
- items tagged with 'blue' and 'red'
- items tagged with 'blue', 'red' and 'green'
SELECT DISTINCT items.*
FROM [your join above]
WHERE tags.name IN ('blue', 'red');
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question: find items tagged with specific tags
Ingo Weiss schrieb:
Thanks, Sebastian!
I have tried this one before. The problem is that it finds all items the
tags of which include EITHER 'blue' OR 'red', not 'blue' AND 'red':
oh ... and ..., i missred
SELECT DISTINCT items.*
FROM items
INNER JOIN taggings
ON items.id = taggings.item_id
INNER JOIN tags
ON tags.id = taggings.tag_id
AND tags.name = 'blue'
AND tags.name = 'red';
or
SELECT DISTINCT items.*
COUNT(items.id)
FROM [your join above]
WHERE tags.name IN ('blue', 'red')
HAVING COUNT(items.id) = 2;
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rewriting query to avoid inline view
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
Any tips greatly appreciated, thanks.
Morten
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? - Original Message - From: Sebastian Mendel [EMAIL PROTECTED] To: liaojian_163 [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, April 23, 2008 2:11 PM Subject: Re: a strange problem liaojian_163 schrieb: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | [...] 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | [...] mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Symlink InnoDB tables without stoping MySQL
Hi, What I'm trying to do is to create a new InnoDB table on a different disk and symlink it to an existing database. I have innodb_file_per_table turned on and here is how I tried to do it mysql \u test mysql create table test (...) ENGINE = 'InnoDB'; mysql\q move the test.ibd file to the other disk create a simlink in the database directory flush tables; This works as expected but there is something that bothers me - I inserted about 60K rows in the new table and all queries I tried are working including selects, inserts and updates. The SHOW TABLE STATUS command displays relevant results and still the test.ibd file to which the symlink points hasn't been changed or accessed at all. Any ideas are welcome Dobromir Velev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
liaojian_163 schrieb: thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? no -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
Hi,
On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl [EMAIL PROTECTED] wrote:
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
You can generate the values with the integers table.
http://www.xaprb.com/blog/2005/12/07/the-integers-table/
Here's an example: http://markmail.org/message/6w46gyijsk5rrj4a
--
Baron Schwartz, Senior Consultant, Percona Inc.
Tel: +1 888 401 3401 ext 507
24/7 Emergency Line +1 888 401 3401 ext 911
Our Services: http://www.percona.com/services.html
Our Blog: http://www.mysqlperformanceblog.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Symlink InnoDB tables without stoping MySQL
Dobromir Velev schrieb: Hi, What I'm trying to do is to create a new InnoDB table on a different disk and symlink it to an existing database. I have innodb_file_per_table turned on and here is how I tried to do it mysql \u test mysql create table test (...) ENGINE = 'InnoDB'; mysql\q move the test.ibd file to the other disk create a simlink in the database directory flush tables; This works as expected but there is something that bothers me - I inserted about 60K rows in the new table and all queries I tried are working including selects, inserts and updates. The SHOW TABLE STATUS command displays relevant results and still the test.ibd file to which the symlink points hasn't been changed or accessed at all. Any ideas are welcome you need to setup per-table tablespace, did you? Section 13.2.3.1, “Using Per-Table Tablespaces”. http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
On Wed, Apr 23, 2008 at 5:42 AM, Morten Primdahl [EMAIL PROTECTED] wrote:
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
Any tips greatly appreciated, thanks.
Morten
First off your porting over or dealing with formerly oracle code, right?
I am not sure if the above syntax is legal in mysql
Here is a shorter, more legal version of what you have above:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date UNION ALL
SELECT DATE('2008-04-02') AS index_date UNION ALL
SELECT DATE('2008-04-03') AS index_date
) AS virtual_date_range
WHERE virtual_date_range.index_date BETWEEN events.start_date AND
events.end_date
GROUP BY index_date;
Here is a start for doing lots of dates
CREATE TABLE integers(i int NOT NULL PRIMARY KEY);
INSERT INTO integers(i) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT ADDDATE( CURDATE( ) , INTERVAL t.i *10 + u.iDAY )
FROM integers AS u, integers AS t
WHERE (t.i *10 + u.i ) 100;
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
Baron Schwartz schrieb:
Hi,
On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl [EMAIL PROTECTED] wrote:
Hi,
A user enters a date range (ie. 2 dates, '2008-04-01' and
'2008-04-03'), the problem is to determine how many open events exist
on each day in this interval.
Assume that the events table has a start_date and an end_date.
One way to solve this problem, is to create an inline view in the
query, eg.:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
matches
FROM events, (
SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
SELECT DATE('2008-04-02') FROM DUAL UNION ALL
SELECT DATE('2008-04-03') FROM DUAL UNION ALL
) AS virtual_date_range
WHERE virtual_date_range.index_date = events.start_date
AND virtual_date_range.index_date = events.end_date
GROUP BY index_date;
This works. But I'm wondering if there's a more elegant way of
expressing the same using pure DML, such that I don't need to build a
huge inline view in case the range is multiple years. Anyone?
A solution that doesn't return any rows for the dates that do not have
an event would work.
Example of the events table and the above query in action:
http://www.pastie.org/185419
You can generate the values with the integers table.
http://www.xaprb.com/blog/2005/12/07/the-integers-table/
i knew that you would answer this ... ;-)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Symlink InnoDB tables without stoping MySQL
Hi, Thanks for pointing it out - I just found the following commands. ALTER TABLE tbl_name DISCARD TABLESPACE; ALTER TABLE tbl_name IMPORT TABLESPACE; I will test it and let you know if it works Thanks Dobromir Velev On Wednesday 23 April 2008 16:27, Sebastian Mendel wrote: Dobromir Velev schrieb: Hi, What I'm trying to do is to create a new InnoDB table on a different disk and symlink it to an existing database. I have innodb_file_per_table turned on and here is how I tried to do it mysql \u test mysql create table test (...) ENGINE = 'InnoDB'; mysql\q move the test.ibd file to the other disk create a simlink in the database directory flush tables; This works as expected but there is something that bothers me - I inserted about 60K rows in the new table and all queries I tried are working including selects, inserts and updates. The SHOW TABLE STATUS command displays relevant results and still the test.ibd file to which the symlink points hasn't been changed or accessed at all. Any ideas are welcome you need to setup per-table tablespace, did you? Section 13.2.3.1, “Using Per-Table Tablespaces”. http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
On Wed, Apr 23, 2008 at 6:31 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: Baron Schwartz schrieb: SQL magic i knew that you would answer this ... ;-) And he did it a minute or so faster than me... (though I did rip off his integers table way back when) -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
Thanks Rob and Baron, I'd never heard of the integers table approach before, really good stuff! First off your porting over or dealing with formerly oracle code, right? Nah, I just learned SQL on Oracle back in the day. DUAL works under MySQL also - don't know since what revision, but it works on 5.0.45 at least - but seeing that I don't need it, I'll stop using it, thanks for the tip! Morten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rewriting query to avoid inline view
On Wed, Apr 23, 2008 at 6:39 AM, Morten Primdahl [EMAIL PROTECTED] wrote: Thanks Rob and Baron, I'd never heard of the integers table approach before, really good stuff! If memory serves postgres has something similar built in, so the syntax is something like seq(1..100) or something like that (I can't remember the function name for the life of me). -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump
Hi I get # mysqldump -u root -p dashboard /home/mysql_backup/dashboard.sql Enter password: mysqldump: Got error: 1033: Incorrect information in file: './dashboard/Actions.frm' when using LOCK TABLES what am i missing Thanks and Regards Kaushal
Upgrading from 4.1 to 5.0
Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Starting a 2nd MySQL instance on UNIX
I have a Solaris box where MySQL 4.0.20 instance is running (to support Bugzilla 2.22). I have loaded mysql5.0 on the same box (for Bugzilla 3.0.3) and created a new mysql50 user that I want to use to run this instance with. I tried to start the instance on another port by running the following command... ./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.0/my.cnf --socket=/tmp/mysql50/mysql.sock --port=3307 --basedir=/usr/local/mysql-5.0 --datadir=/usr/local/mysql-5.0/data --pid-file=/usr/local/mysql-5.0/mysql50.pid --user=mysql50 The instance appears to start but the message Starting the instance comes up and I never get back to the system prompt. it just sits there. If I open another terminal window and do a ps -ef | grep mysql, I can see the new processes running. There is nothing in the error log. I ran the mysql_install_db.sh script to create the mysql database however, I cannot log in. I thought that it creates a root user with no password. I tired logging in as root with no password bu no luck. If i use the mysql 4.0.20 root user password, I get into the 4.0.20 instance even though the mysql50 user does not have mysql 4.0.20 in it's path. when I run mysql at the prompt, how would I differentiate between the 2 instances? So at this point I am stuck. If anyone out can help guide me on what I need to do to ge tthe instance up and running properly, I would appreciate it. I am rather new to MySQL and I have read through the docs but things are still not very clear. Thanks! Mark -- View this message in context: http://www.nabble.com/Starting-a-2nd-MySQL-instance-on-UNIX-tp16834758p16834758.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Symlink InnoDB tables without stopping MySQL
If Linux works the same way as HP-UX (and it should), anything you do to an open file (including deleting it) has no effect until the file is closed. The MySQL server is still using the old file. The next time it stops and restarts, it will follow the symlink. I don't know what the effect of accessing a stale copy of the file will do. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Sebastian Mendel [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 23, 2008 9:27 AM To: Dobromir Velev Cc: mysql@lists.mysql.com Subject: Re: Symlink InnoDB tables without stoping MySQL Dobromir Velev schrieb: Hi, What I'm trying to do is to create a new InnoDB table on a different disk and symlink it to an existing database. I have innodb_file_per_table turned on and here is how I tried to do it mysql \u test mysql create table test (...) ENGINE = 'InnoDB'; mysql\q move the test.ibd file to the other disk create a simlink in the database directory flush tables; This works as expected but there is something that bothers me - I inserted about 60K rows in the new table and all queries I tried are working including selects, inserts and updates. The SHOW TABLE STATUS command displays relevant results and still the test.ibd file to which the symlink points hasn't been changed or accessed at all. Any ideas are welcome you need to setup per-table tablespace, did you? Section 13.2.3.1, Using Per-Table Tablespaces. http://dev.mysql.com/doc/refman/5.0/en/innodb-init.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API routines and cobol
Has anyone successfully called the C API routines for MySQL from COBOL? If so how are the parms defined in COBOL? -- Michael Anderson, J3k Solutions Sr.Systems Programmer/Analyst 832.515.3868 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TO_DAYS Date Range Question
Hello Users- I think I have an interesting question with regards to applying a function to date range, I think half of problem solving is explaining it to an audience so please, bear with me. There is a table Orders that has two DATE columns, StartDate and EndDate. The range of dates can vary from 1 week to years. My goal is to get a count of days that each row in Orders spans over the current financial quarter. Example rows and desired result: OrderId = 1 StartDate '2008-01-01' End Date '2008-06-01' Days in Q2 = 61 OrderId = 2 StartDate '2008-03-01' EndDate '2008-10-01' Days in Q2 = 91 Etc. I can use the TO_DAYS() function to get the absolute count of days difference between Start End, but is there any function that I could apply to limit it to return the days between a range of dates. Another solution I though of trying would be to use a CASE statement to look at each order to see which type of span each Order has (spans entire quarter,starts before the beginning of the quarter but ends in the middle, starts in the middle ends after the end of the quarter, etc.) but this seemed like it might be overkill. Thanks again for reading and if anyone has any ideas I would be very appreciative. David
Slow Queries
I have something I am trying to resolve with an over abundant number of slow queries. Perhaps it is because of some additional indexes needed. As soon as I enabled the option 'log_queries_not_using_indexes = 1' in the configuration file, I started getting messages relating to the select query: SELECT wite_what, wite_desc FROM witelist WHERE wite_what = '$oct1' OR wite_what = '$oct1.$oct2' OR wite_what = '$oct1.$oct2.$oct3' OR wite_what = '$oct1.$oct2.$oct3.$oct4' OR wite_what = '[EMAIL PROTECTED]' OR wite_what = '[EMAIL PROTECTED]' OR wite_what = '$from_dom' OR wite_what = '$rcpt_dom'; The table used is defined as such: mysql desc witelist; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | wite_what | varchar(128) | NO | UNI | NULL|| | wite_desc | varchar(128) | NO | | NULL|| +---+--+--+-+-++ Can anyone shed some light if I should index wite_desc to speed things up? -d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Upgrading from 4.1 to 5.0
You may want to try replication. Setup your replication server as 5.0. That server gives you a chance to play to get things right without affecting the master server. You'll still need to do a dump to get the slave up to speed. Once you get everything right, you can switch over and the slave becomes the master. Very simple in theory, a bit more complicated in practice. Brent Baisley Systems Architect On Apr 23, 2008, at 2:28 PM, Paul Choi wrote: Does anyone have experience with upgrading large databases (~500GB each) from MySQL 4.1 to 5.0? The tables are in InnoDB format. We are using the Community version. I've read that it's recommended that you use mysqldump and then restore, but this is not possible for us, as we cannot have our databases down for long, nor can we have our tables locked while doing dump. I've tried doing the following steps: ibbackup --restore copy over mysql table dirs. set default char set to latin1 (or will default to utf8) in my.cnf because that's the original char set in 4.1 Upgrade only mysql database (user and privilege tables) mysqlcheck --check-upgrade --auto-repair mysql mysql_fix_privilege_tables I've written a script to compare data between the original 4.1 and the new 5.0. Looks like certain rows have different numerical data... so this is not good. I didn't want to do mysql_upgrade on all databases in this instance of MySQL because that resulted in 2 things happening: 1) Don't set default char set to latin1. Run mysql_upgrade Some rows had data truncated in certain columns. 2) Set default char set to latin1. Run mysql_upgrade Copies to TMP table. Takes forever... This is unacceptable for us. What is the recommended way to upgrade from 4.1 to 5.0? Or are we stuck using 4.1 forever? -Paul Choi Plaxo, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aggregation question
I have a table that stores performed transactions and I need to build a histogram of a number of transactions per day in the requested period. So, I made a simple query with the group by clause which returns me what I need: 2008-04-16 65456204 2008-04-17 190838546 2008-04-18 8909047 2008-04-19 9085084 2008-04-21 18221038 2008-04-22 18246184 except that there is no entry for April 20th as there were no transactions at that day. I need a query to return me zero for that day. I.e. I need uninterrupted sequence of dates. I am beating my head at this problem for the whole day and did not make much of a progress. If someone has any idea how to resolve this problem, I'll appreciate a tip greatly. Thank you, Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aggregation question
On Thursday 24 April 2008 12:26:09 pm Gary Greenberg wrote: except that there is no entry for April 20th as there were no transactions at that day. I need a query to return me zero for that day. I.e. I need uninterrupted sequence of dates. I am beating my head at this problem for the whole day and did not make much of a progress. If someone has any idea how to resolve this problem, I'll appreciate a tip greatly. Make a new list of just dates then join them both together. -- Regards, Michael Cole LPIC-1 The man who does not read good books has no advantage over the man who can't read them. - Mark Twain It is our choices, Harry, that show what we truly are, far more than our abilities. — J. K. Rowling Wear the old coat and buy the new book. — Austin Phelps I'm not a teacher: only a fellow traveler of whom you asked the way. I pointed ahead – ahead of myself as well as you. — George Bernard Shaw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Queries
On Wed, Apr 23, 2008 at 9:22 PM, D Hill [EMAIL PROTECTED] wrote: Can anyone shed some light if I should index wite_desc to speed things up? No, since you don't use that column at all. If you're not on MySQL 5, upgrading to MySQL 5 will help. Otherwise, you're best bet is to rewrite the query as UNION clauses with one of your WHERE conditions in each. I know it sounds crazy, but before MySQL 5 the use of indexes with OR queries was not very good. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
