Cannot connect to mysql through network while unix socket works
N,jjkj{ zwkozz
xjDear all,
I have a mysql-4.0.12 server installed on RH 8, it works fine for months before
I
find the server unavailable from the remote host on the same LAN. I am sure
there
isn't any firewall between server and client. I ssh to the server and find the
mysqld
is running and functional, because I can connect to it via unix socket!!!
Things not changed event restart the mysql service, so I have to reboot the
server :(
Anybody here has the same experience, and how this happened?
Oscar Yen.
RE: Re: error
Hi Jim, Your advice is indeed correct for the access denied problem. For your own problem, you might consider taking a look at max_allowed_packet variable of MySQL, as this error is common when you are sending a large blob update and the variable is too small for the update SQL, i.e. max_allowed is 1Mb and you are sending a 2Mb SQL statement. Also the client write and read timeout also causes this error, i.e. when you are doing a query which takes longer than say 60 seconds and your read timeout is set for a default 30 seconds. Kind Regards SciBit MySQL Team http://www.scibit.com MySQL Products: http://www.scibit.com/products/mycon http://www.scibit.com/products/mysqlcomponents http://www.scibit.com/products/mysqlx http://www.scibit.com/products/mascon -Original Message- From: Jim Zipper [EMAIL PROTECTED] To: Emmanuel d [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: error Sent: Thu, 23 Dec 2004 03:58:55 GMT Received: Thu, 23 Dec 2004 04:01:35 GMT Read: Thu, 23 Dec 2004 09:05:20 GMT I am no expert by any means but over the last week I have been trying to solve why I can't connect from W XP as well. I keep getting the error message 2013 lost connection during SQL query. But what I have learned I think is that the error message you have received indicates that you have not set up the proper MySQL user access privileges. As I understand it you need to define access privileges for the client host, user and password. There are wildcard settings and defaults when these fields are left blank. I learned allot from these sections of the MySQL manual http://dev.mysql.com/doc/mysql/en/Privilege_system.html http://dev.mysql.com/doc/mysql/en/User_Account_Management.html but unfortuantely I have still not solved my problem. I don't know if this helps or not but I thought I would try to help. If there is anything you can suggest to solve my problem please respond as well TTFN - Original Message - From: Emmanuel d'Ange [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 3:26 PM Subject: error Hi, I've install odbc 3.51driver on win XP. I've already configure the connector with the correct parameter but when I try to test the connection, I've got this message : [MySQL][ODBC 3.51 Driver]Access denied for user: '[EMAIL PROTECTED]' (using password: yes) I don't know what to do. thanks Best regard e.d'Ange -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc 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]
Tables shortcuts?
Hi everybody. I am rather new to MySQL (and to dba in general), so sorry for the lame question (and for my English). Our company is migrating several Access database to MySQL by now with good results. This process sometimes brings to a reorganization of some key tables that are used by many applications, and are moved from a database to another. I was wondering if there was a way to make something like a shortcut in the original database that can point to the new table position, and so to avoid the rewriting of the code involving the moved table and the assignment of new access rights. Is this a job that can be done using wiews? Thank you in advance for any suggestion. -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Fwd: Re: MUTIPLY function?]
That particular formula cannot handle ANY non-positive number because the LOG() function is undefined for values less than or equal to zero. I just reviewed the archives and realized that this point has never been discussed before (I thought it had). Good catch. well, the slow and dirty formula would be IF(SUM(IF(n=0,1,0))0,0,EXP(SUM(LOG(ABS(COALESCE(n,1)*POWER(-1,SUM(IF(n0,1,0 which simply aggregates the signs and zeroes. here n is the field to multiply. a more performant solution would be a little udf-function. ciao, konrad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alternatives to Oracle's TO_CHAR()
Hi, I am working on a query that was built for Oracle databases but I need to make it compatible with MySQL. Basically I need to convert or cast an INT as a CHAR. The Oracle query just uses TO_CHAR() to achieve this but I am stumped in my search for an equivalent method in MySQL or even an alternative. Any help, hints or suggestions are welcome, Thanks in advance, Philip
Re: Help with a join query please!
Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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]
Data conversion question
Hello, I was looking throught the manual a bit, I haven't found the answer I'm looking for... Does MySQL have commands that would allow me to convert Base64 data to Binary and then convert that Binary to a string format? Thanks, -- Yves Arsenault -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL inadvertently messing with the path in the Windows' registry?
On Wednesday 22 December 2004 09:28 pm, Ney André de Mello Zunino wrote: I have just obtained that conclusive evidence. The MySQL installer is indeed messing up the type of the /Path/ value on the registry, changing it from REG_EXPAND_SZ to REG_SZ. The problem will only take place when you choose to add the /bin/ directory of MySQL to the path, during the installation. Do the developers read these messages as well? If not, where should I report the problem? bugs.mysql.com.. Jeff pgpWdoIKbXEGb.pgp Description: PGP signature
Re: Alternatives to Oracle's TO_CHAR()
On Wednesday 22 December 2004 12:02 pm, Philip Barlow wrote: I am working on a query that was built for Oracle databases but I need to make it compatible with MySQL. Basically I need to convert or cast an INT as a CHAR. The Oracle query just uses TO_CHAR() to achieve this but I am stumped in my search for an equivalent method in MySQL or even an alternative. Any help, hints or suggestions are welcome, Cast will work, its in every DB.. Jeff pgpbdMaJFEcXW.pgp Description: PGP signature
Re: Help with a join query please!
So -- what's the field that relates a booking to an allocation? Do they share a project_ID or what? If they do, you might try this: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = '11' OR B.Project_ID = '11' ORDER BY User_Firstname; soapbox A pet peeve of mine is when people 'quote' NUMBERS. According to the extremely well written manual, you only need to quote STRING values and DATETIME values. Unless the columns Project_ID and Booking_ID are some form of STRING column (CHAR, VARCHAR, TEXT, etc.) you don't need to quote their values in queries. It forces the query engine to perform an unnecessary internal type conversion. Here is what I think your query should look like: SELECT DISTINCT U.User_ID, U.User_Firstname, U.User_Lastname FROM Users U LEFT JOIN Allocations A on A.User_ID = U.User_ID LEFT JOIN Bookings B ON B.User_ID = U.User_ID WHERE A.Project_ID = 11 OR B.Project_ID = 11 ORDER BY User_Firstname; /soapbox I used SELECT DISTINCT so that in the event that someone was both BOOKED and ALLOCATED to the same project, you only got them listed once. Shawn Green Database Administrator Unimin Corporation - Spruce Pine shaun thornburgh [EMAIL PROTECTED] wrote on 12/23/2004 08:37:37 AM: Hi, Thanks for your reply but that produces exactly the same result... Any ideas? From: Sasha Pachev [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Help with a join query please! Date: Tue, 21 Dec 2004 14:57:43 -0700 shaun thornburgh wrote: Hi, I have (among others) three tables in my database that i am struggling with a join query on. The database holds dates for Bookings. If Users are Allocated to a particular Project they can be booked. However if a user is booked but then unallocated I want to be able to display all peolple allocated to that project plus the person originally booked. Here are my efforts so far: SELECT U.User_ID, U.User_Firstname, U.User_Lastname FROM Allocations A, Users U LEFT JOIN Bookings B ON B.User_ID = U.User_ID AND B.Booking_ID = '4512' WHERE U.User_ID = A.User_ID AND A.Project_ID = '11' ORDER BY User_Firstname; Shaun: If I understand the problem right, it sounds like you are missing AND B.Bookings_ID is NULL in the where clause. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- 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]
Re: How to use character_set_xxx in my.ini ?
Hello. I use my.ini in MySQL install directory On Windows, MySQL programs reads startup options from the following files: WINDIR\my.ini C:\my.cnf Use --defaults-file=/path/ command line option to specify exact location of my.ini file. Zimoo [EMAIL PROTECTED] wrote: Hello Gleb, Sunday, December 19, 2004, 10:02:58 PM, you wrote: Also you can put in your [client] section of my.cnf something like: default_character_set=gbk I use my.ini in MySQL install directory, not my.cnf . My MySQL Server in windows services.msc is C:\Program Files\MySQL\bin\mysqld-max-nt --defaults-file=C:\Program Files\mysql\my.ini MySQL When I put default_character_set = gbk or default-character-set = gbk in [client], and use command C:\net start mysql Server could started, but mysql show me like: mysql show variables like char%; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | gbk| | character_set_results| latin1 | | character_set_server | gbk| | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\share\charsets/ | +--++ 7 rows in set (0.00 sec) Why? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump and innodb - set foreign_key_checks=0
Hello. You may execute SET FOREIGN_KEY_CHECKS=0; in mysql and then use source sql.file;. Run mysql with -B command line option. Terence [EMAIL PROTECTED] wrote: Hi, After reading the docs I realise that in order to use mysqldump with innodb tables i need to include SET FOREIGN_KEY_CHECKS=0; at the top of my dump file. Is there anyway to do this when my slave starts up or some other way. The dump file is huge and it takes ages to open and put the line at the top. (In trying to setup replication I am trying all ways to reduce the downtime of the master while I get a snapshot onto the slave) I use ./bin/mysql -u root -p /tmp/dump.sql to get the contents into my slave. Something like this might work: ./bin/mysql -u root -p -e SET FOREIGN_KEY_CHECKS=0; /tmp/dump.sql Grateful for and ideas ... Thanks -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Regarding User creation and loading data
Hello. Your entry in the user table doesn't have the FILE privilege enabled. What output does the following statement produce: show grants for 'your_dba_user'@'your_dba_host'; You can find out your username by executing 'status' command in mysql. Have you reloaded grant tables after changing them? See: http://dev.mysql.com/doc/mysql/en/Access_denied.html http://dev.mysql.com/doc/mysql/en/Privilege_changes.html http://dev.mysql.com/doc/mysql/en/Privileges_provided.html [EMAIL PROTECTED] wrote: Hi, Thank you for your reply. When I log in as a DBA user and try to load data from a file, I am getting an error as Access Denied, so, here I want to know what type of permissions I have to give for a user to load data infile form a file. Thanks, Narasimha -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 22, 2004 1:35 AM To: mysql@lists.mysql.com Subject: Re: Regarding User creation and loading data Hello. What error have you got? Send us compete command that you issued and the error message. Could any one of you help me in creating two users like First user with all dba privileges as root and the Second user with only dml operations. For root user use: grant all privileges on *.* to 'vasja'@'vasinhost' identified by 'vasinpass' with grant option; For dml user use something like this: grant select,insert,update,delete on test.* to 'dml'@'localhost' identified by 'v'; See: http://dev.mysql.com/doc/mysql/en/GRANT.html And in loading data using LOAD DATA INFILE, if I use the file full path, I am getting error. And if I put the file in mysql\bin it is executing. How to load a dta in a file which is located in some other directories. --=0D For technical support contracts, goto https://order.mysql.com/?ref=3Densita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com --=0D MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Confidentiality Notice=0D The information contained in this electronic message and any attachments to= this message are intended for the exclusive use of the addressee(s) and may contain confidential or= privileged information. If you are not the intended recipient, please notify the sender at Wipro or= [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
Thanks Leo, However, remember that the key returns quickly on index-only queries, so the conditions in the WHERE are not my delay at this time. I want to know why it takes 5 mins to scan 2 rows from the MYD Bryan - Original Message - From: Bryan Heitman [EMAIL PROTECTED] To: leo [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 9:37 AM Subject: Re: scanning 2 rows slow index fast 26GB MyISAM Thanks Leo, However, remember that the key returns quickly on index-only queries, so the conditions in the WHERE are not the problem. I want to know why it takes 5 mins to scan 2 rows from the MYD Bryan - Original Message - From: leo [EMAIL PROTECTED] To: Bryan Heitman [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 2:00 AM Subject: Re: scanning 2 rows slow index fast 26GB MyISAM HI,Bryan Heitman, You may change there column `date` to type int,and fill in it with UNIX_TMIESTAMP format value,and alter the key `myKey` on (`AccountID`,`wordid`,`position`,`Date`) if you often use statement like where accountid = xx and wordid = xx and position = 'xx' and date now() - interval 10 day. === 2004-12-22 22:17:00 === I am experiencing extreme slowness performing a query in which 2 rows are returned hanging in the sending data status. Performing an index only query such as SELECT COUNT(*) is extremely quick so I know the only extra step is retrieving the data from the MYD. I am looking for thoughts on why this is slow and what can be done to speed it up. I find it unusual why it would take this long to simply grab 2 rows from the MYD. vmstat reports high reads and strace confirms pread()'s on the MYD file. The only abnormality is my table size MYD is 26 gig and my MYI is about 30 gig. Test system details, tests were performed with no load. System: Redhat Linux 2.4.28 Mysql: tested on versions 4.0.22 and latest 4.1 tree IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at beginning of disk) IDE (WD 2500 JB 8 mb buff) disk2 used for MYI dual xeon 2.4ghz 1gb ddr266 mem Here are query tests detail below: mysql select count(*) from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; +--+ | count(*) | +--+ |2 | +--+ 1 row in set (0.06 sec) mysql select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; ++ | locationid | ++ | 47932 | | 29571 | ++ 2 rows in set (5 min 35.93 sec) mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ CREATE TABLE `location` ( `LocationID` int(11) unsigned NOT NULL auto_increment, `ImapUID` int(11) unsigned NOT NULL default '0', `AccountID` int(11) unsigned NOT NULL default '0', `Date` timestamp(19) NOT NULL, `FromWho` tinyblob, `Subject` tinyblob, `SentTo` tinyblob, `mailbox` varchar(255) default NULL, `body` longblob, PRIMARY KEY (`LocationID`), KEY `myKey` (`LocationID`,`AccountID`,`Date`) ) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] . = = = = = = = = = = = = = = = = = = = = ,! leo [EMAIL PROTECTED] 2004-12-23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: scanning 2 rows slow index fast 26GB MyISAM
Because it should scan through all the table to get all records,so it takes so a long time,i think. Leo, see below in the Extra column, it is not doing a table scan according to explain. mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ - Original Message - From: leo [EMAIL PROTECTED] To: Bryan Heitman [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 9:00 AM Subject: Re: Re: scanning 2 rows slow index fast 26GB MyISAM Bryan Heitman, Because it should scan through all the table to get all records,so it takes so a long time,i think. === 2004-12-23 09:37:00 === Thanks Leo, However, remember that the key returns quickly on index-only queries, so the conditions in the WHERE are not the problem. I want to know why it takes 5 mins to scan 2 rows from the MYD Bryan - Original Message - From: leo [EMAIL PROTECTED] To: Bryan Heitman [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 2:00 AM Subject: Re: scanning 2 rows slow index fast 26GB MyISAM HI,Bryan Heitman, You may change there column `date` to type int,and fill in it with UNIX_TMIESTAMP format value,and alter the key `myKey` on (`AccountID`,`wordid`,`position`,`Date`) if you often use statement like where accountid = xx and wordid = xx and position = 'xx' and date now() - interval 10 day. === 2004-12-22 22:17:00 === I am experiencing extreme slowness performing a query in which 2 rows are returned hanging in the sending data status. Performing an index only query such as SELECT COUNT(*) is extremely quick so I know the only extra step is retrieving the data from the MYD. I am looking for thoughts on why this is slow and what can be done to speed it up. I find it unusual why it would take this long to simply grab 2 rows from the MYD. vmstat reports high reads and strace confirms pread()'s on the MYD file. The only abnormality is my table size MYD is 26 gig and my MYI is about 30 gig. Test system details, tests were performed with no load. System: Redhat Linux 2.4.28 Mysql: tested on versions 4.0.22 and latest 4.1 tree IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at beginning of disk) IDE (WD 2500 JB 8 mb buff) disk2 used for MYI dual xeon 2.4ghz 1gb ddr266 mem Here are query tests detail below: mysql select count(*) from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; +--+ | count(*) | +--+ |2 | +--+ 1 row in set (0.06 sec) mysql select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; ++ | locationid | ++ | 47932 | | 29571 | ++ 2 rows in set (5 min 35.93 sec) mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ CREATE TABLE `location` ( `LocationID` int(11) unsigned NOT NULL auto_increment, `ImapUID` int(11) unsigned NOT NULL default '0', `AccountID` int(11) unsigned NOT NULL default '0', `Date` timestamp(19) NOT NULL, `FromWho` tinyblob, `Subject` tinyblob, `SentTo` tinyblob, `mailbox` varchar(255) default NULL, `body` longblob, PRIMARY KEY (`LocationID`), KEY `myKey` (`LocationID`,`AccountID`,`Date`) ) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/' Best regards, Bryan Heitman FuseMail Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] . = = = = = = = = = = = = = = = = = = = = ,! leo [EMAIL PROTECTED] 2004-12-23 . = = = = = = = = = = = = = = = = = = = = ,! leo [EMAIL PROTECTED] 2004-12-23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
Bryan, Can you send the output of show create table matrix? You've just sent the location table output, but your select command refers to a table called matrix. []s, Sergio. On Wed, 22 Dec 2004, Bryan Heitman wrote: I am experiencing extreme slowness performing a query in which 2 rows are returned hanging in the sending data status. Performing an index only query such as SELECT COUNT(*) is extremely quick so I know the only extra step is retrieving the data from the MYD. I am looking for thoughts on why this is slow and what can be done to speed it up. I find it unusual why it would take this long to simply grab 2 rows from the MYD. vmstat reports high reads and strace confirms pread()'s on the MYD file. The only abnormality is my table size MYD is 26 gig and my MYI is about 30 gig. Test system details, tests were performed with no load. System: Redhat Linux 2.4.28 Mysql: tested on versions 4.0.22 and latest 4.1 tree IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at beginning of disk) IDE (WD 2500 JB 8 mb buff) disk2 used for MYI dual xeon 2.4ghz 1gb ddr266 mem Here are query tests detail below: mysql select count(*) from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; +--+ | count(*) | +--+ |2 | +--+ 1 row in set (0.06 sec) mysql select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; ++ | locationid | ++ | 47932 | | 29571 | ++ 2 rows in set (5 min 35.93 sec) mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ CREATE TABLE `location` ( `LocationID` int(11) unsigned NOT NULL auto_increment, `ImapUID` int(11) unsigned NOT NULL default '0', `AccountID` int(11) unsigned NOT NULL default '0', `Date` timestamp(19) NOT NULL, `FromWho` tinyblob, `Subject` tinyblob, `SentTo` tinyblob, `mailbox` varchar(255) default NULL, `body` longblob, PRIMARY KEY (`LocationID`), KEY `myKey` (`LocationID`,`AccountID`,`Date`) ) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/' Best regards, Bryan Heitman FuseMail Team -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Move Datafiles on server
version: 4.0.22
This is probably documented somewhere, but I can't seem to find it. Can
someone point me in the right direction? I want a setup similar to the
following:
/var/lib/mysql/data/[databases]
/var/lib/mysql/logs/[logs] (binary, etc)
Currently, everything is in /var/lib/mysql (including innondb).
Current my.cnf settings:
log-bin
datadir = /var/lib/mysql/
innodb_data_home_dir =
innodb_log_arch_dir = ./
innodb_log_group_home_dir = ./
My plan is:
1. Shut down mysql
2. for each database cp -r /var/lib/mysql/{db} /var/lib/mysql/data/{db}
3. cp ibdata1 to /var/lib/mysql/data/
4. cp ib_logfile* and ib_arch_log* /var/lib/mysql/logs/
5. cp hostname-bin.* (binary logs and index) to /var/lib/mysql/logs/
6. edit my.cnf as follows
log-bin=/var/lib/mysql/logs/
datadir = /var/lib/mysql/data/
innodb_data_home_dir = /var/lib/mysql/data/
innodb_log_arch_dir = /var/lib/mysql/logs/
innodb_log_group_home_dir = /var/lib/mysql/logs/
7. Restart mysql
Is there anything else that I am missing? Are there any gotchas with this
setup?
Thanks
-
Do you Yahoo!?
Send holiday email and support a worthy cause. Do good.
Re: MySQL inadvertently messing with the path in the Windows' registry?
Jeff Smelser wrote: Do the developers read these messages as well? If not, where should I report the problem? bugs.mysql.com.. Thanks. I already posted a bug report. FWIW, here is the bug tracking URL: http://bugs.mysql.com/bug.php?id=7510. Regards, -- Ney André de Mello Zunino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables shortcuts?
- Original Message - From: Nico Alberti [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 10:25 AM Subject: Re: Tables shortcuts? On Thu, 23 Dec 2004 08:46:13 -0500, Rhino [EMAIL PROTECTED] wrote: I am not very clear on what you want to do. Your English is fine but you haven't explained the problem and what you want to do in sufficient detail. Thank you Rhino for your answer. I would like to do a thing like this: A table, say: olddb.table1 is moved to newdb.table1. Every application (let's forget about Access now) has to change its query accordingly. What I was asking is if there is some trick that can make appear a bogus table1 in olddb that references to the new position of table1, so any query can work as before affecting the real table1 in newdb. For what I know this could be a job that can be done by a view (even if, using 4.1 I can not use them). Of course this is not a blocking problem, I was only wondering if there was a way to save me some work :-) Nico, It is always best if you post followup questions/remarks back to the list. This makes it possible for everyone on the list to follow the conversation and to help you. It also ensures that the conversation will be stored on the MySQL archive so that others can learn from it in the future. That is why I am sending this reply to the list, not just to you directly. I don't know of a way to do what you want to do in MySQL. I am relatively new to MySQL myself. I just looked in the manual and didn't see anything that does what you want to do but maybe I just didn't look in the right place. It sounds like you are describing something like a Unix symbolic link but I don't know of any way to do that within MySQL. Maybe someone else on the list has an idea that can help you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_fetch_lengths()
We were running mysql 3.22.30 on an Tru 64 Alpha server OSF 4.0F. We
recently upgraded to mysql 3.23.58 on the same server.
In the code fragment below, there is a memory leak at line 8 because
mysql_fetch_lenghts returns an array of unsigned long integers representing
the size of each column. So, has the retun value of mysql_fetch_lengths()
been the same for mysql 3.22.30 and 3.23.58?
Thank you ,
-Teresa
--
1. unsigned long *lengths;
2. unsigned int num_fields;
3. unsigned int i;
4. MYSQL_RES *result=NULL;
5. row = mysql_fetch_row(result);
6. if (row)
7. {
8. len = malloc(sizeof(unsigned long) * mysql_num_fields(result));
9. num_fields = mysql_num_fields(result);
10.lengths = mysql_fetch_lengths(result);
11.for(i = 0; i num_fields; i++)
12.{
13. printf(Column %u is %lu bytes in length.\n, i, lengths[i]);
14.}
15.free(len)
16.}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
My mistake! Here you go:
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
- Original Message -
From: Sergio Salvi [EMAIL PROTECTED]
To: Bryan Heitman [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 23, 2004 10:08 AM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
Bryan,
Can you send the output of show create table matrix? You've just sent
the location table output, but your select command refers to a table
called matrix.
[]s,
Sergio.
On Wed, 22 Dec 2004, Bryan Heitman wrote:
I am experiencing extreme slowness performing a query in which 2 rows are
returned hanging in the sending data status.
Performing an index only query such as SELECT COUNT(*) is extremely quick
so
I know the only extra step is retrieving the data from the MYD.
I am looking for thoughts on why this is slow and what can be done to
speed
it up. I find it unusual why it would take this long to simply grab 2
rows
from the MYD. vmstat reports high reads and strace confirms pread()'s on
the MYD file.
The only abnormality is my table size MYD is 26 gig and my MYI is about
30
gig.
Test system details, tests were performed with no load.
System: Redhat Linux 2.4.28
Mysql: tested on versions 4.0.22 and latest 4.1 tree
IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at
beginning of disk)
IDE (WD 2500 JB 8 mb buff) disk2 used for MYI
dual xeon 2.4ghz
1gb ddr266 mem
Here are query tests detail below:
mysql select count(*) from matrix where accountid = 11 and wordid = 72
and
position = 'Body' and date now() - interval 10 day;
+--+
| count(*) |
+--+
|2 |
+--+
1 row in set (0.06 sec)
mysql select locationid from matrix where accountid = 11 and wordid = 72
and position = 'Body' and date now() - interval 10 day;
++
| locationid |
++
| 47932 |
| 29571 |
++
2 rows in set (5 min 35.93 sec)
mysql explain select locationid from matrix where accountid = 11 and
wordid
= 71 and position = 'Body' and date now() - interval 10 day;
++--+---+---+-+---+---+-+
| table | type | possible_keys | key | key_len | ref |
rows
| Extra |
++--+---+---+-+---+---+-+
| matrix | ref | myKey | myKey | 9 | const,const,const |
56909 | Using where |
++--+---+---+-+---+---+-+
CREATE TABLE `location` (
`LocationID` int(11) unsigned NOT NULL auto_increment,
`ImapUID` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`Date` timestamp(19) NOT NULL,
`FromWho` tinyblob,
`Subject` tinyblob,
`SentTo` tinyblob,
`mailbox` varchar(255) default NULL,
`body` longblob,
PRIMARY KEY (`LocationID`),
KEY `myKey` (`LocationID`,`AccountID`,`Date`)
) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/'
--
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]
Re: Tables shortcuts?
Yes, it should be possible to 'alias' an entire table through a view (assuming your version of MySQL has views). Views will be updateable so long as none of the columns are computed. That means that a view based on SELECT * FROM tablename should give you two options a) you can call your VIEWs columns anything you want (so you can match your legacy table's old names) b) it will be updateable. You can read from and write to the view just as you would have the original table. HOWEVER!! The view will still have to obey any constraints placed on the source table. You will only be able to insert and update columns presented in the view. Base table columns not presented in the view will be invisible. I have no idea if a query using two or more tables can act as the base definition of an updateable view. Another option could be to look at what the developers are calling federated databases. That works like a linked table (to use an M$ term) in that you have a table name in your database (local reference) but the data actually resides on a different server. Both are coming soon to production-ready MySQL. Right now both features are in the testing and development phases. My advice is to check out the new 5.x+ and test it to discover what works and what doesn't for what you would like it to do. Perhaps it is stable enough to meet your needs but I leave that determination up to you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 12/23/2004 11:30:34 AM: - Original Message - From: Nico Alberti [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 10:25 AM Subject: Re: Tables shortcuts? On Thu, 23 Dec 2004 08:46:13 -0500, Rhino [EMAIL PROTECTED] wrote: I am not very clear on what you want to do. Your English is fine but you haven't explained the problem and what you want to do in sufficient detail. Thank you Rhino for your answer. I would like to do a thing like this: A table, say: olddb.table1 is moved to newdb.table1. Every application (let's forget about Access now) has to change its query accordingly. What I was asking is if there is some trick that can make appear a bogus table1 in olddb that references to the new position of table1, so any query can work as before affecting the real table1 in newdb. For what I know this could be a job that can be done by a view (even if, using 4.1 I can not use them). Of course this is not a blocking problem, I was only wondering if there was a way to save me some work :-) Nico, It is always best if you post followup questions/remarks back to the list. This makes it possible for everyone on the list to follow the conversation and to help you. It also ensures that the conversation will be stored on the MySQL archive so that others can learn from it in the future. That is why I am sending this reply to the list, not just to you directly. I don't know of a way to do what you want to do in MySQL. I am relatively new to MySQL myself. I just looked in the manual and didn't see anything that does what you want to do but maybe I just didn't look in the right place. It sounds like you are describing something like a Unix symbolic link but I don't know of any way to do that within MySQL. Maybe someone else on the list has an idea that can help you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables shortcuts?
If MySQL works like DB2 - in most respects, they behave the same - a view based on a Join is ALWAYS read-only. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql mysql@lists.mysql.com; Nico Alberti [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 11:54 AM Subject: Re: Tables shortcuts? Yes, it should be possible to 'alias' an entire table through a view (assuming your version of MySQL has views). Views will be updateable so long as none of the columns are computed. That means that a view based on SELECT * FROM tablename should give you two options a) you can call your VIEWs columns anything you want (so you can match your legacy table's old names) b) it will be updateable. You can read from and write to the view just as you would have the original table. HOWEVER!! The view will still have to obey any constraints placed on the source table. You will only be able to insert and update columns presented in the view. Base table columns not presented in the view will be invisible. I have no idea if a query using two or more tables can act as the base definition of an updateable view. Another option could be to look at what the developers are calling federated databases. That works like a linked table (to use an M$ term) in that you have a table name in your database (local reference) but the data actually resides on a different server. Both are coming soon to production-ready MySQL. Right now both features are in the testing and development phases. My advice is to check out the new 5.x+ and test it to discover what works and what doesn't for what you would like it to do. Perhaps it is stable enough to meet your needs but I leave that determination up to you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 12/23/2004 11:30:34 AM: - Original Message - From: Nico Alberti [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 10:25 AM Subject: Re: Tables shortcuts? On Thu, 23 Dec 2004 08:46:13 -0500, Rhino [EMAIL PROTECTED] wrote: I am not very clear on what you want to do. Your English is fine but you haven't explained the problem and what you want to do in sufficient detail. Thank you Rhino for your answer. I would like to do a thing like this: A table, say: olddb.table1 is moved to newdb.table1. Every application (let's forget about Access now) has to change its query accordingly. What I was asking is if there is some trick that can make appear a bogus table1 in olddb that references to the new position of table1, so any query can work as before affecting the real table1 in newdb. For what I know this could be a job that can be done by a view (even if, using 4.1 I can not use them). Of course this is not a blocking problem, I was only wondering if there was a way to save me some work :-) Nico, It is always best if you post followup questions/remarks back to the list. This makes it possible for everyone on the list to follow the conversation and to help you. It also ensures that the conversation will be stored on the MySQL archive so that others can learn from it in the future. That is why I am sending this reply to the list, not just to you directly. I don't know of a way to do what you want to do in MySQL. I am relatively new to MySQL myself. I just looked in the manual and didn't see anything that does what you want to do but maybe I just didn't look in the right place. It sounds like you are describing something like a Unix symbolic link but I don't know of any way to do that within MySQL. Maybe someone else on the list has an idea that can help you. Rhino -- 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]
Re: mysql_fetch_lengths()
In the last episode (Dec 23), Teresa A Narvaez said:
We were running mysql 3.22.30 on an Tru 64 Alpha server OSF 4.0F. We
recently upgraded to mysql 3.23.58 on the same server.
In the code fragment below, there is a memory leak at line 8 because
mysql_fetch_lenghts returns an array of unsigned long integers
representing the size of each column. So, has the retun value of
mysql_fetch_lengths() been the same for mysql 3.22.30 and 3.23.58?
1. unsigned long *lengths;
2. unsigned int num_fields;
3. unsigned int i;
4. MYSQL_RES *result=NULL;
5. row = mysql_fetch_row(result);
6. if (row)
7. {
8. len = malloc(sizeof(unsigned long) * mysql_num_fields(result));
9. num_fields = mysql_num_fields(result);
10.lengths = mysql_fetch_lengths(result);
11.for(i = 0; i num_fields; i++)
12.{
13. printf(Column %u is %lu bytes in length.\n, i, lengths[i]);
14.}
15.free(len)
16.}
The memory allocated at line 8 is freed at line 15. In fact, it's
never used at all. The array returned by mysql_fetch_lengths is an
internal array that is freed by mysql_free_result(); you don't need to
allocate it or free it.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to change root password - getting error
Hi, I am trying to change my root password for mysql 4.1.7 running on Linux (as I forgot it) using the docs found at: HYPERLINK http://dev.mysql.com/doc/mysql/en/Resetting_permissions.htmlhttp://dev.mys ql.com/doc/mysql/en/Resetting_permissions.html When I get to the step: shell mysqladmin -u root flush-privileges password newpwd I get the error: mysqladmin: unable to change password; error: 'Can't find any matching row in the user table' I think my user table is screwed up but how can I get in to fix it??? Worst case, do I just uninstall and reinstall mysql? Thanks, Don --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.822 / Virus Database: 560 - Release Date: 12/22/2004
Re: Tables shortcuts?
I know that with M$ SQL server, if a view is based on a JOIN and provides a deterministic recordset, then the view could be updateable. But like everything else they put out, if it's a day ending with Y and between 10 and 11 AM on the third odd Tuesday of the calendar season, the planets will align and all will be good.(sarcasm) It's still the better idea to stick with single table queries to define the dataset of an updateable view. However, I have total faith in the MySQL developers that if it's possible, they will make multiple-table updateable views work and work well. If not, well, we will be no worse off. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 12/23/2004 12:12:03 PM: If MySQL works like DB2 - in most respects, they behave the same - a view based on a Join is ALWAYS read-only. Rhino - Original Message - From: [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Cc: mysql mysql@lists.mysql.com; Nico Alberti [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 11:54 AM Subject: Re: Tables shortcuts? Yes, it should be possible to 'alias' an entire table through a view (assuming your version of MySQL has views). Views will be updateable so long as none of the columns are computed. That means that a view based on SELECT * FROM tablename should give you two options a) you can call your VIEWs columns anything you want (so you can match your legacy table's old names) b) it will be updateable. You can read from and write to the view just as you would have the original table. HOWEVER!! The view will still have to obey any constraints placed on the source table. You will only be able to insert and update columns presented in the view. Base table columns not presented in the view will be invisible. I have no idea if a query using two or more tables can act as the base definition of an updateable view. Another option could be to look at what the developers are calling federated databases. That works like a linked table (to use an M$ term) in that you have a table name in your database (local reference) but the data actually resides on a different server. Both are coming soon to production-ready MySQL. Right now both features are in the testing and development phases. My advice is to check out the new 5.x+ and test it to discover what works and what doesn't for what you would like it to do. Perhaps it is stable enough to meet your needs but I leave that determination up to you. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Rhino [EMAIL PROTECTED] wrote on 12/23/2004 11:30:34 AM: - Original Message - From: Nico Alberti [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED] Sent: Thursday, December 23, 2004 10:25 AM Subject: Re: Tables shortcuts? On Thu, 23 Dec 2004 08:46:13 -0500, Rhino [EMAIL PROTECTED] wrote: I am not very clear on what you want to do. Your English is fine but you haven't explained the problem and what you want to do in sufficient detail. Thank you Rhino for your answer. I would like to do a thing like this: A table, say: olddb.table1 is moved to newdb.table1. Every application (let's forget about Access now) has to change its query accordingly. What I was asking is if there is some trick that can make appear a bogus table1 in olddb that references to the new position of table1, so any query can work as before affecting the real table1 in newdb. For what I know this could be a job that can be done by a view (even if, using 4.1 I can not use them). Of course this is not a blocking problem, I was only wondering if there was a way to save me some work :-) Nico, It is always best if you post followup questions/remarks back to the list. This makes it possible for everyone on the list to follow the conversation and to help you. It also ensures that the conversation will be stored on the MySQL archive so that others can learn from it in the future. That is why I am sending this reply to the list, not just to you directly. I don't know of a way to do what you want to do in MySQL. I am relatively new to MySQL myself. I just looked in the manual and didn't see anything that does what you want to do but maybe I just didn't look in the right place. It sounds like you are describing something like a Unix symbolic link but I don't know of any way to do that within MySQL. Maybe someone else on the list has an idea that can help you. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Optimising a query on a large table.
I have a 152MB MyISAM table that I am trying to execute a simple select statement on, I need to retreave all rows with a given index, sorted by date. This is taking a very long period of time to execute. What can I do to speed up the query. The sql is, SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start '2004-09-01 00:00:00') AND (session_start '2004-10-01 00:00:00') Order by session_start Thanks Rob Keeling - -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
On Thu, 23 Dec 2004, Bryan Heitman wrote:
My mistake! Here you go:
Ok, no prob :)
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
Oops, I forgot to ask you to send the output of show index from matrix.
But your index myKey looks goods, you could try changing the order of
the fields in your key. Try creating a index with your fields ordered by
the Cardinality value from the show index from matrix output
(in asceding order).
Also, what happens if you don't specify the date value in your query?
Check the time it takes and the explain output.
Another thing I would suggest is to create (or replace) your index,
trying all (or almost all) of the possible combinations regarding the
order of the keys in your index. It helped me in some situations, and
sometimes it's better for me to keep two indices with the same keys but
different order, because of my different selects.
Hope that helps!
[]s,
Sergio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tables shortcuts?
On Thu, 23 Dec 2004 11:30:34 -0500, Rhino [EMAIL PROTECTED] wrote: It is always best if you post followup questions/remarks back to the list. Sorry, wrong button. -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_fetch_lengths()
Thank you for the response. I completely agree with your response. The
reason why I asked this question is because I remember seeing len(unsigned
long *lengths;) dynamically allocated in the MYSQL manual some time
ago(when I was running mysql 3.23.30). So, I wonder if there was a change
in the return value of mysql_fetch_lenghts() in mysql 3.23.58.
Otherwise, I made a mistake in dynamically allocating memory for len. I
must have misunderstood.
Thank you.
-Teresa
This is a PRIVATE message. If you are not the intended recipient, please
delete without copying and kindly advise us by e-mail of the mistake in
delivery. NOTE: Regardless of content, this e-mail shall not operate to
bind CSC to any order or other contract unless pursuant to explicit written
agreement or government initiative expressly permitting the use of e-mail
for such purpose.
Dan Nelson
dnelson To: Teresa A
Narvaez/FED/[EMAIL PROTECTED]
@allantgroup.com cc: mysql@lists.mysql.com
Subject: Re:
mysql_fetch_lengths()
12/23/2004 12:19
PM
In the last episode (Dec 23), Teresa A Narvaez said:
We were running mysql 3.22.30 on an Tru 64 Alpha server OSF 4.0F. We
recently upgraded to mysql 3.23.58 on the same server.
In the code fragment below, there is a memory leak at line 8 because
mysql_fetch_lenghts returns an array of unsigned long integers
representing the size of each column. So, has the retun value of
mysql_fetch_lengths() been the same for mysql 3.22.30 and 3.23.58?
1. unsigned long *lengths;
2. unsigned int num_fields;
3. unsigned int i;
4. MYSQL_RES *result=NULL;
5. row = mysql_fetch_row(result);
6. if (row)
7. {
8. len = malloc(sizeof(unsigned long) * mysql_num_fields(result));
9. num_fields = mysql_num_fields(result);
10.lengths = mysql_fetch_lengths(result);
11.for(i = 0; i num_fields; i++)
12.{
13. printf(Column %u is %lu bytes in length.\n, i, lengths[i]);
14.}
15.free(len)
16.}
The memory allocated at line 8 is freed at line 15. In fact, it's
never used at all. The array returned by mysql_fetch_lengths is an
internal array that is freed by mysql_free_result(); you don't need to
allocate it or free it.
--
Dan Nelson
[EMAIL PROTECTED]
--
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]
Re: Tables shortcuts?
On Thu, 23 Dec 2004 11:54:30 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Yes, it should be possible to 'alias' an entire table through a view Thank you all for your help. Of course the production server is 4.1, but I will start immediately to test version 5 -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising a query on a large table.
Rob Keeling wrote: I have a 152MB MyISAM table that I am trying to execute a simple select statement on, I need to retreave all rows with a given index, sorted by date. This is taking a very long period of time to execute. What can I do to speed up the query. The sql is, SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start '2004-09-01 00:00:00') AND (session_start '2004-10-01 00:00:00') Order by session_start Thanks Rob Keeling - -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams Rob, Have you run show full processlist; to see if it is copying to a tmp table ?? walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising a query on a large table.
kernel [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Rob, Have you run show full processlist; to see if it is copying to a tmp table ?? walt Nothing listed other than the query itself. Should have said its on Mysql 3.21. Rob Keeling -- I love deadlines. I love the whooshing noise they make as they go by. - Douglas Adams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query question
I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TINYTEXT field uniqueness question
When specifying an index for TEXT and BLOB types, you must specify a length. as an example... CREATE TABLE test ( sValue TINYTEXT NOT NULL, UNIQUE KEY(sValue(90)) ) Denis Gerasimov wrote: Hello, Is that possible to ensure uniqueness for a TINYTEXT field? I tried to create an index (with UNIQUE constraint) but my GUI tool always says me 'Duplicate entry 'X' for key N' Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
This will return the top 50 urls in descending order of popularity. SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; Feel free to adjust as needed. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ed Lazor [EMAIL PROTECTED] wrote on 12/23/2004 01:45:30 PM: I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TINYTEXT field uniqueness question
To me, that error means that you ALREADY HAVE duplicates in your data. You will have to eliminate the dupes before you can create the unique index. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Chris [EMAIL PROTECTED] wrote on 12/23/2004 01:55:34 PM: When specifying an index for TEXT and BLOB types, you must specify a length. as an example... CREATE TABLE test ( sValue TINYTEXT NOT NULL, UNIQUE KEY(sValue(90)) ) Denis Gerasimov wrote: Hello, Is that possible to ensure uniqueness for a TINYTEXT field? I tried to create an index (with UNIQUE constraint) but my GUI tool always says me 'Duplicate entry 'X' for key N' Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_fetch_lengths()
In the last episode (Dec 23), Teresa A Narvaez said: Dan wrote: In the last episode (Dec 23), Teresa A Narvaez said: We were running mysql 3.22.30 on an Tru 64 Alpha server OSF 4.0F. We recently upgraded to mysql 3.23.58 on the same server. In the code fragment below, there is a memory leak at line 8 because mysql_fetch_lenghts returns an array of unsigned long integers representing the size of each column. So, has the retun value of mysql_fetch_lengths() been the same for mysql 3.22.30 and 3.23.58? The memory allocated at line 8 is freed at line 15. In fact, it's never used at all. The array returned by mysql_fetch_lengths is an internal array that is freed by mysql_free_result(); you don't need to allocate it or free it. Thank you for the response. I completely agree with your response. The reason why I asked this question is because I remember seeing len(unsigned long *lengths;) dynamically allocated in the MYSQL manual some time ago(when I was running mysql 3.23.30). So, I wonder if there was a change in the return value of mysql_fetch_lenghts() in mysql 3.23.58. Otherwise, I made a mistake in dynamically allocating memory for len. I must have misunderstood. I don't think it has ever required the user to malloc or free the lengths array. I checked back as far as 3.20.32, and the mysql_fetch_lengths function is identical to 3.23.58, except for the change from int* to long*. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql-4.1.8 library name bug
SQL Hello, All! IMHO this the bug in 4.1.8 to create library shared libraries without .so suffix. After this any programs linked with static libraries not dynamic, because of convention lib*.so.[0-9] in shared libraries names. ldconfig doesn't see new libraries at all too. This bug in 4.1.8 4.1.7 compiled on the same PC at the same time with .so suffix in library names. Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport . +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising a query on a large table.
Hi Rob! Am Don, 2004-12-23 um 18.54 schrieb Rob Keeling: I have a 152MB MyISAM table that I am trying to execute a simple select statement on, I need to retreave all rows with a given index, sorted by date. This is taking a very long period of time to execute. Can you give the time a bit more exact? What can I do to speed up the query. You might tell us the exact table definition, and that of any indexes you created. The sql is, SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start '2004-09-01 00:00:00') AND (session_start '2004-10-01 00:00:00') Order by session_start How would you define the likeness of numbers? Even though this seems to work for you, I would always code a numeric comparison with a numeric operator: person_id = 10 HTH and Merry Christmas, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-4.1.8 library name bug
Yes, this has been reported on this list a couple times already. Really baffling how this one made it out of QA. Josh On Thu, 23 Dec 2004 21:43:27 +0200 (EET) Andrey Kotrekhov [EMAIL PROTECTED] wrote: SQL Hello, All! IMHO this the bug in 4.1.8 to create library shared libraries without .so suffix. After this any programs linked with static libraries not dynamic, because of convention lib*.so.[0-9] in shared libraries names. ldconfig doesn't see new libraries at all too. This bug in 4.1.8 4.1.7 compiled on the same PC at the same time with .so suffix in library names. Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport ÔÅÌ. +380 562 34-00-44 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Ed, Try the following query select ID, DateAdded, URL, count(*) as 'cnt' from mytable group by URL order by cnt desc It should display the most numerous URLs in the table. dimitar -Original Message- From: Ed Lazor [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 1:46 PM To: 'mysql' Subject: Query question I use a table to log what pages on the website are getting visits with a table structure like this: ID DateAdded URL Now I'm trying to query the database to see which URLs are most popular, but I'm not sure how to go about doing this. Any ideas? Thanks, Ed -- 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]
Re: scanning 2 rows slow index fast 26GB MyISAM
Hi Sergio,
All of your suggestions deal with key optimization, I do not believe I have
a key issue here. Remember that select count(*), an index-only query
returns in .06 seconds which is very quick. The real question, is why does
it take 5 mins to retrieve the row data for these 2 rows that the index
retrieved so quickly. Why the delay and why the heavy read activity on the
MYD file.
That to me does not make a lot of sense on the time it takes, does MyISAM
not handle large MYD files w/ a billion rows that well where I should split
my data across many tables instead? I have certainly not ran across this
issue before, but this is the first time I have a table with a billion rows.
mysql show index from matrix;
+++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---+-+--++--++-+
| matrix | 1 | myKey|1 | AccountID | A |
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|2 | WordID | A |
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|3 | Position| A |
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|4 | date| A |
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey2 |1 | LocationID | A |
NULL | NULL | NULL | | BTREE | |
+++--+--+-+---+-+--++--++-+
5 rows in set (0.00 sec)
- Original Message -
From: Sergio Salvi [EMAIL PROTECTED]
To: Bryan Heitman [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 23, 2004 12:01 PM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
On Thu, 23 Dec 2004, Bryan Heitman wrote:
My mistake! Here you go:
Ok, no prob :)
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
Oops, I forgot to ask you to send the output of show index from matrix.
But your index myKey looks goods, you could try changing the order of
the fields in your key. Try creating a index with your fields ordered by
the Cardinality value from the show index from matrix output
(in asceding order).
Also, what happens if you don't specify the date value in your query?
Check the time it takes and the explain output.
Another thing I would suggest is to create (or replace) your index,
trying all (or almost all) of the possible combinations regarding the
order of the keys in your index. It helped me in some situations, and
sometimes it's better for me to keep two indices with the same keys but
different order, because of my different selects.
Hope that helps!
[]s,
Sergio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
On Thursday 23 December 2004 02:01 pm, Bryan Heitman wrote: Hi Sergio, All of your suggestions deal with key optimization, I do not believe I have a key issue here. Remember that select count(*), an index-only query returns in .06 seconds which is very quick. The real question, is why does it take 5 mins to retrieve the row data for these 2 rows that the index retrieved so quickly. Why the delay and why the heavy read activity on the MYD file. Do a show table status. It shows the number of rows in a table. It knows this well before you run select count(*), so it really doesnt count anything. So the select count(*) theory isnt correct. Jeff pgpywUqJPtAY0.pgp Description: PGP signature
RE: Query question
Thanks, Shawn. I didn't think count would just limit to the items being grouped - very handy =) -Ed SELECT URL, count(1) as popularity FROM yourtablename GROUP BY URL ORDER BY popularity DESC LIMIT 50; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: scanning 2 rows slow index fast 26GB MyISAM
Bryan Heitman wrote: I am experiencing extreme slowness performing a query in which 2 rows are returned hanging in the sending data status. Performing an index only query such as SELECT COUNT(*) is extremely quick so I know the only extra step is retrieving the data from the MYD. I am looking for thoughts on why this is slow and what can be done to speed it up. I find it unusual why it would take this long to simply grab 2 rows from the MYD. vmstat reports high reads and strace confirms pread()'s on the MYD file. The only abnormality is my table size MYD is 26 gig and my MYI is about 30 gig. Test system details, tests were performed with no load. System: Redhat Linux 2.4.28 Mysql: tested on versions 4.0.22 and latest 4.1 tree IDE (WD 2500 JB 8 mb buff) disk1 used for MYD (written contiguously at beginning of disk) IDE (WD 2500 JB 8 mb buff) disk2 used for MYI dual xeon 2.4ghz 1gb ddr266 mem Here are query tests detail below: mysql select count(*) from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; +--+ | count(*) | +--+ |2 | +--+ 1 row in set (0.06 sec) mysql select locationid from matrix where accountid = 11 and wordid = 72 and position = 'Body' and date now() - interval 10 day; ++ | locationid | ++ | 47932 | | 29571 | ++ 2 rows in set (5 min 35.93 sec) mysql explain select locationid from matrix where accountid = 11 and wordid = 71 and position = 'Body' and date now() - interval 10 day; ++--+---+---+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++--+---+---+-+---+---+-+ | matrix | ref | myKey | myKey | 9 | const,const,const | 56909 | Using where | ++--+---+---+-+---+---+-+ CREATE TABLE `location` ( `LocationID` int(11) unsigned NOT NULL auto_increment, `ImapUID` int(11) unsigned NOT NULL default '0', `AccountID` int(11) unsigned NOT NULL default '0', `Date` timestamp(19) NOT NULL, `FromWho` tinyblob, `Subject` tinyblob, `SentTo` tinyblob, `mailbox` varchar(255) default NULL, `body` longblob, PRIMARY KEY (`LocationID`), KEY `myKey` (`LocationID`,`AccountID`,`Date`) ) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA DIRECTORY='/home/SQL/' INDEX DIRECTORY='/home/SQL/' 1) Do you have cardinalities for the following columns? accountid wordid position date 2) Do you have the EXPLAIN for the SELECT query you posted? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: scanning 2 rows slow index fast 26GB MyISAM
Bryan,
Select count(*) is basically a different query then select locationid or any
of your fields. I have tables with way more than a billion rows of
information, I have some in innodb and some in myisam, and neither of them
when heavily loaded will take as long as yours is taking.
I recommend that you try this:
Run and Explain:
select locationid from matrix where accountid = 11 and wordid = 72
and position = 'Body';
Who many results do you get?
Then run and explain:
select locationid from matrix where accountid = 11 and wordid = 72
and position = 'Body' and date now() - interval 10 day;
How many results to you get on this query?
I am betting the problem is that you only have results in the past 10 days
and nothing before that with accounted =11, worded =72 and position = Body.
Which would then do a scan on the date, since it doesn't have anything
before that. Just a theory.
Donny
-Original Message-
From: Bryan Heitman [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 23, 2004 2:02 PM
To: Sergio Salvi
Cc: mysql@lists.mysql.com
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
Hi Sergio,
All of your suggestions deal with key optimization, I do not believe I
have
a key issue here. Remember that select count(*), an index-only query
returns in .06 seconds which is very quick. The real question, is why
does
it take 5 mins to retrieve the row data for these 2 rows that the index
retrieved so quickly. Why the delay and why the heavy read activity on
the
MYD file.
That to me does not make a lot of sense on the time it takes, does MyISAM
not handle large MYD files w/ a billion rows that well where I should
split
my data across many tables instead? I have certainly not ran across this
issue before, but this is the first time I have a table with a billion
rows.
mysql show index from matrix;
+++--+--+-+---
+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
|
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---
+-+--++--++-+
| matrix | 1 | myKey|1 | AccountID | A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|2 | WordID | A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|3 | Position| A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|4 | date| A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey2 |1 | LocationID | A
|
NULL | NULL | NULL | | BTREE | |
+++--+--+-+---
+-+--++--++-+
5 rows in set (0.00 sec)
- Original Message -
From: Sergio Salvi [EMAIL PROTECTED]
To: Bryan Heitman [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 23, 2004 12:01 PM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
On Thu, 23 Dec 2004, Bryan Heitman wrote:
My mistake! Here you go:
Ok, no prob :)
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT
NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=10 AVG_ROW_LENGTH=300 DATA
DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
Oops, I forgot to ask you to send the output of show index from
matrix.
But your index myKey looks goods, you could try changing the order of
the fields in your key. Try creating a index with your fields ordered by
the Cardinality value from the show index from matrix output
(in asceding order).
Also, what happens if you don't specify the date value in your query?
Check the time it takes and the explain output.
Another thing I would suggest is to create (or replace) your index,
trying all (or almost all) of the possible combinations regarding the
order of the keys in your index. It helped me in some situations, and
sometimes it's better for me to keep two indices with the same keys but
different order, because of my different selects.
Hope that helps!
[]s,
Sergio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To
SELECT INTO OUTFILE with UNION
Greetings from Austin, TX: What is the proper syntax for SELECT INTO OUTFILE in a statement with a UNION keyword, like the following: SELECT Addresses FROM editors UNION SELECT Addresses FROM authors Thanks, Lynn Bender UnsubCentral Secure Email List Suppression Management Neutral. Bonded. Trusted. You are receiving this commercial email from a representative of UnsubCentral, Inc. 13171 Pond Springs Road, Austin, TX 78729 Toll Free: 800.589.0445 To cease all communication with UnsubCentral, visit http://www.unsubcentral.com/unsubscribe or send an email to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimising a query on a large table.
Rob,
First of all I would say, your query is pretty badly laid out. First,
unless you need every fields from a table returned only ask for those
specific fields, and do you have an index on the combination of person_id +
session_start? If not, your query will always be slow.
But this is how I would write it.
Select bla, bla2 from table
where person_id = 10
and session_start between ('2004-09-01 00:00:00' and '2004-10-01 00:00:00')
Then why would you order by session_start, when the odds are that you added
the data to the table by time anyway. So why waste the servers time
ordering something that may already be ordered for you automatically. But
you would know that better than any of us.
A query like this should take no longer than 0.1 seconds to execute in most
cases, even with a few gigs of data.
Doonny
-Original Message-
From: news [mailto:[EMAIL PROTECTED] On Behalf Of Rob Keeling
Sent: Thursday, December 23, 2004 11:55 AM
To: mysql@lists.mysql.com
Subject: Optimising a query on a large table.
I have a 152MB MyISAM table that I am trying to execute a simple select
statement on,
I need to retreave all rows with a given index, sorted by date.
This is taking a very long period of time to execute.
What can I do to speed up the query.
The sql is,
SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start
'2004-09-01 00:00:00') AND (session_start '2004-10-01 00:00:00') Order
by
session_start
Thanks
Rob Keeling
-
--
I love deadlines. I love the whooshing noise they make as they go by.
- Douglas Adams
--
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]
Re: mysql_fetch_lengths()
Thank you very much for your help! -Teresa This is a PRIVATE message. If you are not the intended recipient, please delete without copying and kindly advise us by e-mail of the mistake in delivery. NOTE: Regardless of content, this e-mail shall not operate to bind CSC to any order or other contract unless pursuant to explicit written agreement or government initiative expressly permitting the use of e-mail for such purpose. Dan Nelson dnelson To: Teresa A Narvaez/FED/[EMAIL PROTECTED] @allantgroup.com cc: mysql@lists.mysql.com Subject: Re: mysql_fetch_lengths() 12/23/2004 02:14 PM In the last episode (Dec 23), Teresa A Narvaez said: Dan wrote: In the last episode (Dec 23), Teresa A Narvaez said: We were running mysql 3.22.30 on an Tru 64 Alpha server OSF 4.0F. We recently upgraded to mysql 3.23.58 on the same server. In the code fragment below, there is a memory leak at line 8 because mysql_fetch_lenghts returns an array of unsigned long integers representing the size of each column. So, has the retun value of mysql_fetch_lengths() been the same for mysql 3.22.30 and 3.23.58? The memory allocated at line 8 is freed at line 15. In fact, it's never used at all. The array returned by mysql_fetch_lengths is an internal array that is freed by mysql_free_result(); you don't need to allocate it or free it. Thank you for the response. I completely agree with your response. The reason why I asked this question is because I remember seeing len(unsigned long *lengths;) dynamically allocated in the MYSQL manual some time ago(when I was running mysql 3.23.30). So, I wonder if there was a change in the return value of mysql_fetch_lenghts() in mysql 3.23.58. Otherwise, I made a mistake in dynamically allocating memory for len. I must have misunderstood. I don't think it has ever required the user to malloc or free the lengths array. I checked back as far as 3.20.32, and the mysql_fetch_lengths function is identical to 3.23.58, except for the change from int* to long*. -- Dan Nelson [EMAIL PROTECTED] -- 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]
Re: scanning 2 rows slow index fast 26GB MyISAM
Hi Donny,
Remember that the Date field is also indexed:
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`);
Whether it is a SELECT COUNT(*) or a SELECT locationid, it still must
evaluate the Date field, in both cases it should and does (according to
explain) use the key: myKey w/o a table-scan required.
The select as you suggest w/o the date key is the same speed only it returns
thousands of results, this query would make sense why it is slow to retrieve
data. However, my previous query which only returns 2 results should be
fast and not slow.
You are right that SELECT COUNT(*) is a different query. My point is the
only difference is a SELECT locationid must retrieve the results as
locationid is not in the index. It should not however take 5 mins to return
2 rows from the table.
Bryan
- Original Message -
From: Donny Simonton [EMAIL PROTECTED]
To: 'Bryan Heitman' [EMAIL PROTECTED]; 'Sergio Salvi'
[EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 23, 2004 2:49 PM
Subject: RE: scanning 2 rows slow index fast 26GB MyISAM
Bryan,
Select count(*) is basically a different query then select locationid or
any
of your fields. I have tables with way more than a billion rows of
information, I have some in innodb and some in myisam, and neither of them
when heavily loaded will take as long as yours is taking.
I recommend that you try this:
Run and Explain:
select locationid from matrix where accountid = 11 and wordid = 72
and position = 'Body';
Who many results do you get?
Then run and explain:
select locationid from matrix where accountid = 11 and wordid = 72
and position = 'Body' and date now() - interval 10 day;
How many results to you get on this query?
I am betting the problem is that you only have results in the past 10 days
and nothing before that with accounted =11, worded =72 and position =
Body.
Which would then do a scan on the date, since it doesn't have anything
before that. Just a theory.
Donny
-Original Message-
From: Bryan Heitman [mailto:[EMAIL PROTECTED]
Sent: Thursday, December 23, 2004 2:02 PM
To: Sergio Salvi
Cc: mysql@lists.mysql.com
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
Hi Sergio,
All of your suggestions deal with key optimization, I do not believe I
have
a key issue here. Remember that select count(*), an index-only query
returns in .06 seconds which is very quick. The real question, is why
does
it take 5 mins to retrieve the row data for these 2 rows that the index
retrieved so quickly. Why the delay and why the heavy read activity on
the
MYD file.
That to me does not make a lot of sense on the time it takes, does MyISAM
not handle large MYD files w/ a billion rows that well where I should
split
my data across many tables instead? I have certainly not ran across this
issue before, but this is the first time I have a table with a billion
rows.
mysql show index from matrix;
+++--+--+-+---
+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation
|
Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+++--+--+-+---
+-+--++--++-+
| matrix | 1 | myKey|1 | AccountID | A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|2 | WordID | A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|3 | Position| A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey|4 | date| A
|
NULL | NULL | NULL | | BTREE | |
| matrix | 1 | myKey2 |1 | LocationID | A
|
NULL | NULL | NULL | | BTREE | |
+++--+--+-+---
+-+--++--++-+
5 rows in set (0.00 sec)
- Original Message -
From: Sergio Salvi [EMAIL PROTECTED]
To: Bryan Heitman [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Thursday, December 23, 2004 12:01 PM
Subject: Re: scanning 2 rows slow index fast 26GB MyISAM
On Thu, 23 Dec 2004, Bryan Heitman wrote:
My mistake! Here you go:
Ok, no prob :)
CREATE TABLE `matrix` (
`WordID` int(11) unsigned NOT NULL default '0',
`LocationID` int(11) unsigned NOT NULL default '0',
`Position` enum('Body','From','Subject','To','Mailbox','File') NOT
NULL
default 'Body',
`times` int(11) unsigned NOT NULL default '0',
`MyOrder` int(11) unsigned NOT NULL default '0',
`AccountID` int(11) unsigned NOT NULL default '0',
`date` timestamp(19) NOT NULL,
KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
KEY `myKey2` (`LocationID`)
) TYPE=MyISAM MAX_ROWS=10
Re: Data conversion question
Does MySQL have commands that would allow me to convert Base64 data to Binary and then convert that Binary to a string format? have a look at the attached file (hope the attachment did not get stripped). it contains some udf-functions for base64 en/de-coding. a little description is included. ciao, konrad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trick to remember sort order?
Is there a trick to remember the sort order? I want to update some field to be the ordinal of the record according to some sort criteria. update record set ordinal=CURRENT_ROW() order by xx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: trick to remember sort order?
I'm not sure I understand what your asking. Order by can be used for asc or desc (http://dev.mysql.com/doc/mysql/en/Sorting_rows.html). If you are looking to update a column in a specific order then you are using the correct syntax(http://dev.mysql.com/doc/mysql/en/UPDATE.html). I hope those links help you because I do not understand your question. Respectfully, Ligaya Turmelle --- Life is a game... so have fun. --- www.PHPCommunity.org Open Source, Open Community Visit for more information or to join the movement Dave Dyer wrote: Is there a trick to remember the sort order? I want to update some field to be the ordinal of the record according to some sort criteria. update record set ordinal=CURRENT_ROW() order by xx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Help, Two Tables...
Hi I have two tables; tbl_Headers and tbl_SubItems. tbl_Headers contain my Header Items such as (fields: ID HEADER) ID HEADER --- 1.00 TOPIC ONE 2.00 TOPIC TWO 3.00 TOPIC THREE tbl_SubItems contain Sub Header Items such as (fields: ID SUBITEM) ID SUBITEM --- 1.01 SubItem 1 1.02 SubItem 2 1.03 SubItem 3 2.01 SubItem 1 2.02 SubItem 2 3.01 SubItem 1 3.02 SubItem 2 etc and so on ... I want to output a list like so: 1.00 TOPIC ONE 1.01 SubItem 1 1.02 SubItem 2 1.03 SubItem 3 2.00 TOPIC TWO 2.01 SubItem 1 2.02 SubItem 2 3.00 TOPIC THREE 3.01 SubItem 1 3.02 SubItem 2 etc and so on ... I'm brain-cramping on this, can someone please help ? Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: TINYTEXT field uniqueness question
Thanks to all, but I solved this problem by converting this field to VARCHAR(255) (the same to TINYTEXT) type. Chris, I tried your solution, it works but TINYTEXT's index behavior seems to be very odd. E.g. if length is set to 3 it will never complain about duplicate 'ab' but shows error in case of duplicate 'abcd' (= 3 chars). Why? Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Thursday, December 23, 2004 9:56 PM To: MySQL General List Subject: Re: TINYTEXT field uniqueness question When specifying an index for TEXT and BLOB types, you must specify a length. as an example... CREATE TABLE test ( sValue TINYTEXT NOT NULL, UNIQUE KEY(sValue(90)) ) Denis Gerasimov wrote: Hello, Is that possible to ensure uniqueness for a TINYTEXT field? I tried to create an index (with UNIQUE constraint) but my GUI tool always says me 'Duplicate entry 'X' for key N' Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- 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]
