RE: 6.5 seconds seems slow for the following search?
Big tables, a join and a LIKE on middleweight hardware - looks about right to me. Peter -Original Message- From: Kyle Renfro [mailto:[EMAIL PROTECTED] Sent: 27 March 2004 00:48 To: [EMAIL PROTECTED] Subject: 6.5 seconds seems slow for the following search? I am testing MySQL as a possible replacement for our proprietary db server for several large databases. I really want MySQL but I am getting what seems like slow search times. Does 6.5 seconds seem slow/typical/fast for the following search? SELECT main.ownername FROM main, rolledplate WHERE rolledplate.platenum LIKE '3^6SP%' AND main.recid = rolledplate.recid; The 'main' table has 21+ million records. The 'rolledplate' table has 144+ million records. The EXPLAIN gives pretty optimal results and I have tried the select syntax several different ways. The recid field is the PK in the main table. In both tables RECID is an unsigned int with a 1:M relationship between main and rolledplate. The tables are MyISAM with a fixed row format. System Specs: P4 2.8 Ghz 1 Gb RAM Serial ATA disks (data/indexes split on 2 disks) MySQL 4.1.1 The my.cnf is a modified my-huge.cnf with info gleened from web/list. Our in-house db beats the pants off of this (but on better hardware). I'm not sure if there is some optimization I have missed, but I have tried everything I can think of. Any suggestions would be greatly appreciated. Server settings or anything else available on request. thanks! -- 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: Deleting a database
Ola Would DROP DATABASE databasename; not do it? I beleive, and I may be wrong, the only other way to delete JUST the tables is as you mentioned ( delete table gl_story, gl_submit,... OR drop table gl_story, gl_submit,... ) -- Jeremy Oliver Bluejays PC Repair tel 0208 656 1056 mob 07 855 833 401 www.bluejayspc.co.uk Don't forget to see our latest system offerings at our Sales website. www.bluejayspcs.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
purge logs problem
hi listers i have 'log-bin' enabled in my.cnf for replication purposes. once in so many days i remove them using syntax purge master logs to 'logname'. this is fine and works ever time for all lognames i specify. but today i have a problem with 1 of the 7 i had to delete (the last 1 for that matter, the other were purged). i specify the logname to purge but when i 'show master logs', the logname is still listed. i rerun the purge command and but its still listed. i get no error message when i run the purge command neither are any entries listed in hostname.err. mysql version: 4.0.13-max-log os: linux 2.4.20-18.7smp tks tom
Re: invalid zip file...
On 26.03.2004 17:12 (+0100), Bruno wrote: I tried to download mysqlgui-win32-static-1.7.5-2.zip, but it doesn't appear to be a valid archive... What should I do?? Depending on its size, re-download it and see if it was simply a download error. If this won't help, see the file's header. If it doesn't start with PK, it's really no ZIP file. If I'd know where to find this file, I could take a look at it. -- Yves Goergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TEST MAIL
This is a test amil, please ignore -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ||Cel: +58-412-8859934 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORDER BY WITH NO PRINT
This may sound a little bit off, but what programming language are you using
to print this. It might be easier to simply use a language like perl to
print out the output that you are suggesting.
From: Seena Blace [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: ORDER BY WITH NO PRINT
Date: Fri, 26 Mar 2004 19:33:44 -0800 (PST)
Hello,
I want to show outpur like this?
Group hostname details
aa abababa
aa abababababab
aa anannanananna
bbllololololool
bbssjjsjsjsjjsjsj
Select group,hostname,details from table1 order by group;
I want the output should be like
Group hostname details
aa abababa
abababababab
anannanananna
bbllololololool
ssjjsjsjsjjsjsj
please advice.
thx -seena
_
Get tax tips, tools and access to IRS forms all in one place at MSN Money!
http://moneycentral.msn.com/tax/home.asp
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug in MySQL with Correlated Subqueries?
MySQL 4.1.1-alpha incorrectly computes select-list correlated subqueries. See the explanation of the problem below. Is this a known problem? Thanks! Consider the following schema and data: CREATE TABLE person (pid INTEGER, name CHAR(5)); CREATE TABLE phone (pid INTEGER, num CHAR(10)); INSERT INTO PERSON VALUES (1, 'Bob'); INSERT INTO PERSON VALUES (2, 'Jane'); INSERT INTO PHONE VALUES (1, '12345'); INSERT INTO PHONE VALUES (1, '23456'); INSERT INTO PHONE VALUES (2, '34567'); Now consider the following query: mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P; +--+---+ | PID | C | +--+---+ |1 | 2 | |2 | 0 | +--+---+ 2 rows in set (0.00 sec) Obviously the count for PID=2 is incorrect. If I run the same query with an ORDER BY, I get mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE H.pid = P.pid) AS C FROM person P ORDER BY pid DESC; +--+---+ | PID | C | +--+---+ |2 | 1 | |1 | 0 | +--+---+ 2 rows in set (0.00 sec) Now PID=1 is wrong. It appears to always mess up the last element. What's up? I ran this on MySQL 4.1.1-alpha under both Linux (RedHat) and Windows with the same results. I start up the server as follows: bin\mysqld-max --standalone --console --ansi --default-table-type=innodb __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 6.5 seconds seems slow for the following search?
In the last episode (Mar 26), Kyle Renfro said: I am testing MySQL as a possible replacement for our proprietary db server for several large databases. I really want MySQL but I am getting what seems like slow search times. Does 6.5 seconds seem slow/typical/fast for the following search? SELECT main.ownername FROM main, rolledplate WHERE rolledplate.platenum LIKE '3^6SP%' AND main.recid = rolledplate.recid; The 'main' table has 21+ million records. The 'rolledplate' table has 144+ million records. The EXPLAIN gives pretty optimal results and I have tried the select syntax several different ways. The recid field is the PK in the main table. In both tables RECID is an unsigned int with a 1:M relationship between main and rolledplate. The tables are MyISAM with a fixed row format. What's the EXPLAIN look like, and how many records do you get from the above query: total, and average per rollplate.recid (i.e what's M)? 6 seconds could be high if you are returning only a couple of records, but if you're returning over 600 records, than it's reasonable (assuming slow disks at 100 seeks/sec). Since you're only fetching one field, creating multicolumn indexes on rolledplate (platenum, recid) and main (recid, ownername) may let you avoid table lookups altogether. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL
Dear all, Is it possible to install mysql locally on Sun Solaris 8 as I do not have root permission ? If so, what is the expected total size on disk and recommended steps (./configure, make, make --prefix=/my/home/ install : is this sufficient) ? Any hints or pointers are much appreciated. Thank you very much. Regards, Adaikalavan Ramasamy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Directory question for the masters...
I need to access a specific directory from within tomcat, but I'm having a specific problem and require some advice. I have a link to a file in the servlet, something like https://myserver.com/special-directory/file.zip; If I create the directory as follows: /export/home/tomcat/webapps/ROOT/special-directory/ and put file.zip in that directory, it all works perfectly. HOWEVER, if instead of creating the actual directory, I create a symbolic link to a different directory (i.e. in ROOT I type ln -s /export/home/myfiles/directory special-directory, the linked directory appears, and (in unix) I can see/access the files. BUT - tomcat no longer finds the files, even though the linked directory has the required name and files. Is there some way to make tomcat see a linked directory? The problem is that the servlets are writing a data file for offline use in one directory, and then I was hoping to symbolically link that directory under ROOT so the user could access it. Worst case, I guess I could write to ROOT/special-directory, but I didn't really want the user files stored there - just a link. Since these files are created on the fly, a hard link won't work. Thanks very much in advance, -Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in MySQL with Correlated Subqueries?
At 13:59 27/3/2004, Ed Smith wrote: Hi, Below the results from a server built with BK 4.1 tree 3 days ago: C:\mysql\binmysqld --standalone --console --ansi --default-table-type=innodb 040328 0:36:59 InnoDB: Started; log sequence number 0 43634 mysqld: ready for connections. Version: '4.1.2-alpha-max-debug' socket: '' port: 3306 C:\mysql\binmysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.2-alpha-max-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE person (pid INTEGER, name CHAR(5)); Query OK, 0 rows affected (0.17 sec) mysql CREATE TABLE phone (pid INTEGER, num CHAR(10)); Query OK, 0 rows affected (0.16 sec) mysql mysql mysql INSERT INTO PERSON VALUES (1, 'Bob'); Query OK, 1 row affected (0.07 sec) mysql INSERT INTO PERSON VALUES (2, 'Jane'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO PHONE VALUES (1, '12345'); Query OK, 1 row affected (0.07 sec) mysql INSERT INTO PHONE VALUES (1, '23456'); Query OK, 1 row affected (0.05 sec) mysql INSERT INTO PHONE VALUES (2, '34567'); Query OK, 1 row affected (0.06 sec) mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE - H.pid = P.pid) AS C FROM person P; +--+---+ | PID | C | +--+---+ |1 | 2 | |2 | 1 | +--+---+ 2 rows in set (0.01 sec) mysql SELECT PID, (SELECT COUNT(*) FROM PHONE H WHERE - H.pid = P.pid) AS C - FROM person P ORDER BY pid DESC; +--+---+ | PID | C | +--+---+ |2 | 1 | |1 | 2 | +--+---+ 2 rows in set (0.00 sec) Regards, For technical support contracts, visit https://order.mysql.com/ Are you MySQL certified?, http://www.mysql.com/certification/ Miguel Angel Solórzano [EMAIL PROTECTED] São Paulo - Brazil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reading Binary Data
Hi Folks -- Been working on this issue for a while, and not making much headway, so I thought I'd throw it out to the list and see what wisdom came back :) Problem: I'm trying to find a way to store LOTS of scientific data (mostly 32-bit integers and floating point numbers) in a database for analysis work. The instrument(s) we're working with generate very large volumes of numeric data, roughly 30GB per day. What I'd like to do is have a method read through the data files produced, store the data in tables, making it easy to subset and index the data. OK, doesn't sound too bad off the bat. Here's the issue: Is there a way to store data in BINARY numeric formats into the database (without converting to strings first)? In two or three of my tables, rows contain 2500-3000 individual numbers, which would be a logistical nightmare to throw into a query string, not to mention potential loss of accuracy from rouding errors in floating point numbers. I thought that LOAD DATA INFILE was the next solution, but it appears to, when using fixed-row format, is that it uses the DISPLAY sizes of the fields, rather than the byte storage requirements for them. Anyone else out there dealing with large volumes of numeric data? Thanks in advance, Ken === Diplomacy is the weapon of the Civilized Warrior - Hun, A.T. Ken Gieselman [EMAIL PROTECTED] System Administrator http://www.endlessknot.com/~kgieselm Endlessknot Communications http://www.endlessknot.com === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Your archive
Your file is attached. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How stable is 4.0.18?
Pete Harlan wrote: My two cents: Just upgrade. We hammer on it pretty hard and the transition has not only been smooth, it's been a delight. The query cache, on our load, handles 60% of the queries, which I never imagined would happen. 4.0.18 feels as boringly stable as most released MySQLs :) Well, I did it. And it went remarkably smooth, just like you said. :) One question, though. I am correct in my assumption that, with the query_cache and all, I can reduce key_buffer_size? It is now set to 64M (against a 128M query_cache_size). My guess is that I will need less of key_buffer_size space. Right? Thanks! - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compiling MySQL 4.1.1 For New OpenSSL Version
I downloaded the Linux RPM binaries for MySQL 4.1.1 and all except MySQL-shared-4.1.1-0.i386.rpm and MySQL-shared-compat-4.1.1-0.i386.rpm installed correctly. The -shared RPM failed on missing dependencies, it was looking for openssl 0.9.6 and libcrypto 0.9.6 shared libraries and couldn't find them. This is because I had just upgraded that machine system to OpenSSL 0.9.7. (The *-shared-compat* RPM failed to install for the same reason.) So I downloaded and installed the source code RPM for MySQL 4.1.1. I changed the release number (as my only change to the SPEC file) in the version string and am now running rpmbuild to force a recompile of the package. Since this is on a quite old 550 Mhz machine, the compile is taking a very long time, but it seems to be chugging along normally. When it finishes, I hope to have a MySQL flavor compiled against openssl 0.9.7 and a shiny new MySQL-shared binary RPM that will install correctly. My question is, can I just install the shared RPM and leave the other precompiled binaries I installed (downloaded from MySQL AB) alone? Or should I install all my locally compiled RPM binaries? Thanks a lot Bob Cochran Greenbelt, Maryland, USA http://greenbeltcomputer.biz/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MY APOLOGIES!!!
Very Sorry!!! I sent a Tomcat request to this list by mistake. Please ignore it. Thanks, -Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
