help with SELECT BETWEEN

I want to create a SELECT statement using BETWEEN like: SELECT * FROM mytable WHERE myfield BETWEEN value_1 AND value_2. The field I'm applying my BETWEEN clause is a varchar. Now, if value_1 and value_2 are numbers the select statement works as is. If value_1 and value_2 are characters I need

mysql4.0

Hi Is mysql 4.0 still supported or EOL, as the recent advisory says users of 4.0 should upgrade to 4.1 to patch the security problem. Subsequently I have noticed a new 5.x release and new 4.1.x release and no 4.0.x release. Thanks Chris -- MySQL General Mailing List For list archives: http

Re: Case confusion

eep/delete. May not be the best way, but it would work. Chris Marcus Bointon wrote: I just noticed that a key field (emailaddress) in my db is case sensitive when it should not have been, so now I've got a bunch of what are effectively duplicate records. I'm having trouble picking them

Re: automatic timestamp

Nestor wrote: People, I have the following table: DROP TABLE IF EXISTS bid; CREATE TABLE bid ( bid_id int(11) NOT NULL auto_increment, bid_proj_name varchar(100) NOT NULL default '', bid_prop_name varchar(100) NOT NULL default '', bid_amount varchar(20) NULL default '', bid_sub_name varchar

Re: Question on mysqldump --single-transaction

Dominik Klein wrote: Hi everyone I'm wondering about the "--single-transaction" option on mysqldump. Documentation says --- This option issues a BEGIN SQL statement before dumping data from the server. ... --- So does this include the entire dump in one transaction? Or is it one transaction

Re: add users in mysql 5.0 ubuntu dapper drake

Dimitri Mallis wrote: hi list firstly am in the right place to ask this sort of question i installed mysql 5.0 from the repos on ubuntu dapper drake i could only go in to mysql when i was root, & then i guess i am suposed to add users there. You've got things confused. 'mysql' the service,

Re: Dynamic link

z247 wrote: Is this the only option? I get Access denied. CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; MySQL said: Documentation #1227 - Access denied; you need the SUPER privilege for this operation Read the error message. You don't have "super"

Re: binary select - case sensitive

kalin mintchev wrote: hi all... i found this on the mysql dev manual site: "MySQL 4 and later string comparisons, including DISTINCT, aren't case sensitive unless the field is declared as BINARY or you use BINARY in your comparison." so here i tried it but no good. any ideas?! the field is not

Re: add users in mysql 5.0 ubuntu dapper drake

Always CC the list. You will get better / faster responses. CREATE USER user [IDENTIFIED BY [PASSWORD] 'password'] thats is the syntax it tells me to use so i typed CREATE USER dimitri [IDENTIFIED BY [PASSWORD] '**']; were the starts are my password, i hav tried fiddling with the syntax but

Re: Error in statement

Neil Tompkins wrote: I've the following SQL statement which I created in MS Access. My table layout is the same in mySQL but it says I have an error and the query doesn't run SELECT AccommodationOverview.Title, AccommodationOverview.AccommodationOverviewID, AccommodationOverview.Accommodati

Re: Odd join questing

Chris W wrote: It may not be odd to many but I can't seem to think of how to do this I have 2 queries below that give me exactly what I want each to do but now I want to combing them is such a way that in the second query I have one additional column that gives me a 1 on a row wher

Re: slow mysql...

Ritesh Shetty wrote: Hello, My mysql server sometime just slows responding to database queriesIt is as slow as less than 1 per sec. But it speeds up by itself after some time , I have noticed that when it speeds up there are lot more MYsql thread than just one thread when it is slow. So

Re: Help: PHP won't connect to MySQL

Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it's own, I've set the the database and etc., but nothing happened whe

Re: Mysql cluster so slow...

Xueron Nee wrote: Hi all, There is a table contains about 60,000 rows. where select from this table with 'order by xxx' statement, it is tooo slow. but if i do it without 'order by xxx', it works fine. Is there any tips and suggestion for me? Thanks! Add an index to the 'xxx' column? That

Re: Table 'mysql.host' doesn't exist

thomas Armstrong wrote: I created this very-simple '/etc/my.conf': [mysqld] basedir=/usr/local/mysql datadir=/usr/local/mysql/var socket=/tmp/mysql.sock --- But it doesn't still work. '/usr/local/mysql/var' hosts the data, but within 'mysql' subdirectory, there are only two fil

Re: arrays in stored procedures - pl. help

L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data

Re: load-file() doesn't work

[EMAIL PROTECTED] wrote: I'm using MySQL 3.23.36 on RH 7.1. I've created a table photos (in database album) with the following colums: ID NUM AUTO_INCREMENT PRIMARY KEY IMAGE MEDIUMBLOB I'm running mysql from the command line: mysql -u root 'root' has all privileges. The file I'm trying to lo

Re: Help: PHP won't connect to MySQL

Kay C. Tien wrote: At 05:37 PM 7/17/2006 Monday, Chris wrote: Kay C. Tien wrote: Hi All, The simple script I'm testing works on a Linux server. I got tired of having to upload and test them, so I decided to install MySQL on my computer, which is running Win XP. MySQL works fine on it&

Re: problem with double data

Obed Soto Déctor wrote: Hi, i'm sorry for my english, i'm from México and i'm gonna do my best, i've made a store procedure but i have some problems becouse when a make a simple operation with double datas the result is not what i wait. We can't tell much without seeing at least the stored pro

Re: Incremental Backup

Dan Trainor wrote: Kaushal Shriyan wrote: Hi ALL I would like to know following 2 things. 1) if i would like to take mysql incremental backup weekly cp /var/lib/mysql/ /backup/mysqlbak/ how can i do it by above command as i would like to run cron weekly for take mysql backup. i would like t

Re: Can not find mysql_config

Fauzan Azhary wrote: Hi All, I've tried to upgrade mysql from MySQL 4.1.12 to MySQL 5.0.22. Before upgrading, I have like below : # rpm -qa | grep mysql mysql-4.1.12-3.RHEL4.1 mysql-server-4.1.12-3.RHEL4.1 mysqlclient10-3.23.58-4.RHEL4.1 mysql-devel-4.1.12-3.RHEL4.1 And after upgrading, I have

Re: Database or table level logging?

David Felio wrote: Is it possible to have general query type logging on just a specific database or table? I don't want all queries to all databases, I just need a log of accesses to a specific table. I figure I can use stored procedures or a separate mysql instance as a workaround if I need to

Re: multiple InnoDB read-only server instances on same file system

pradhuman jhala wrote: I use MySQL version 4.1.18 with redhat-linux-gnu (i686). I have created a InnoDB database consisting of 210 GB ibdata files. I used InnoDB to allocate more memory and speed up the load. I want use it for read-only purpose and it works fine with one MySQL server instance. I

Re: Incremental Backup

Kaushal Shriyan wrote: Hi ALL Can we have incremental backup of MySQL Database and if its there How do we do it Reposting your question again won't change the responses you have received (it'll actually annoy people more than anything else). -- MySQL General Mailing List For list archives:

Re: mysqld-nt 100% CPU Utilization?

Robinson, Eric wrote: Our MySQL-based medical application has been running fine for 3 years. Last week, mysqld-nt started jumping up to 100% CPU and staying there until someone restarts the service. mysqlcheck found no errors in the database. I dumped it to disk and read it back in to create a fr

Re: Can Innodb reuse the deleted rows disk space?

leo huang wrote: hi, Dilipkumar Thank you very much! I think I know the fact: The Innodb can't reuse the deleted rows' disk space. And a solution is: dump the data; shutdown mysql; delete the files; restart mysql; import the data. InnoDB does re-use the space inside the database, it's the log

Re: Will UPDATE block on SELECT?

Ratheesh K J wrote: I would like to know whether a SELECT query would block an Update on the same table. The table is of InnoDB type. Since InnoDB tables apply row level locks should the Update queries be blocked until the select query completes? I experienced such a scenario wherein an update

Re: Returning results as a field name

ame, n.question_id, n2.answer from blah n, blah n2 where n.name=n2.name and n.answer=n2.answer; +---+-++ | name | question_id | answer | +---+-++ | chris | 1 | yes| | chris | 2 | no | +---+-++ 2 r

Re: How to "control" the number of sql dameons?

Prem wrote: Hi All, Could you please explain why some one needs multiple sql daemons and how to control the number of dameons. It forks so it can handle multiple queries at the same time. The main process (mysqld_safe) handles starting up the other processes and various other things (I assu

Re: Can Innodb reuse the deleted rows disk space?

leo huang wrote: hi, Chris Thank you for your advice! I know that Innodb use the logfiles circularly. Can Innodb re-use the deleted rows' disk space in tablespace? I'm sure it will, what makes you think it won't? You might need an 'optimize table' or something to s

Re: hi, I am not able view all the records in the tabel

Annam Srinivas wrote: Hi, Problem is like this. There is table with 'table_name-1' where, when I am executing the query like ' select * from table_name-1;' it is showing only 432 records but acctually there are 539 records in the table. when I select individual records from the table it is sh

Re: MySQL With a HUGE Config

Dyego Souza Dantas Leal wrote: I have a Dell P2800 SERVER WITH: Red Hat AS 4 ( 64 bit machine) 6 GB of RAM Dual Xeon DUALCORE 2.8Ghz with 2MB of L2 Cache I will use MySQl 5.0 and InnoDB, exits a config to use all power of this server ? the developer guys can send-me a config ? There is a

Re: why size of table c united from table a and b are bigger than a+b ?

chylli wrote: I run following command : use db1; insert into db2.c select a.a, a.b, a,c, b.d, b,e ... from a left join b on (a.id=b.id); Do you have an index on a.id and b.id ? size of table a and table b is: [EMAIL PROTECTED] ls -l ../db1/a.* -rw-rw 1 mysql mysql 9230 May 10 15:41

Re: newbie..foreign key clarification

Grass Cake wrote: I'm still unsure about foreign keys..even after reading the doc file and Paul Dubois 3rd edition My doubt is...how much constraint is applied.( probably a bad explanation) If i have a table 'Customers' with the primary key being 'CustID' Then i have a table 'LastVisit' wi

Re: allow access to all users within a network..

bruce wrote: hi... i'm trying to figure out how to allow all users on machines within my network access to a mysql db... i've tried: grant access all on *.* to '*'@'%' grant access all on *.* to '%'@'%' grant access all on *.* to @'%' with no luck.. What errors do you get when you try to

Re: Mysqlcheck issues

Dirk Bremer wrote: I am using MySQL server 4.1.10 on Windows 2003 Server with MyISAM tables. I have an issue where occasionally an index (MYI) file becomes corrupted. I do not know why this occurs. To combat this issue, I tried running the following command every half-hour: mysqlcheck -Aamov --a

Re: why size of table c united from table a and b are bigger than a+b ?

chylli wrote: chylli <[EMAIL PROTECTED]> writes: Chris <[EMAIL PROTECTED]> writes: Because you're adding all columns from a and b into it. It is too slow. Has anyone better methods to do that work? Drop the indexes on c and create them at the end. Each row that&#x

Re: Can Innodb reuse the deleted rows disk space?

leo huang wrote: hi, Chris I'm sure it will, what makes you think it won't? Because some paper say that when the row is deleted or update, Innodb just make a mark that the row is deleted and it didn't delete the rows. I can't find more information about the re-use tablesp

Re: allow access to all users within a network..

Asif Lodhi wrote: Hi Chris, On 7/28/06, Chris <[EMAIL PROTECTED]> wrote: > i'm trying to figure out how to allow all users on machines within my > network access to a mysql db... > > with no luck.. ... Have you

Re: select random ids from list

kalin mintchev wrote: hi all... how can i do a query on a list of ids without doing individual queries for each one of them? something like: select id,title from content where id = 100,106,109; select id,title from content where id IN(10,20,30); -- MySQL General Mailing List For list ar

Re: Slow query log: "administrator command: quit" ?

TK wrote: My MySQL server (4.0.20, Linux) was running slowly. I checked the slow queries log, and found many of these during the problem period: # Time: 060730 20:44:40 # [EMAIL PROTECTED]: xxx [] # Query_time: 68 Lock_time: 0 Rows_sent: 0 Rows_examined: 2 # administrator command: Quit; # [

Re: select between date

Penduga Arus wrote: On 7/31/06, Peter Lauri <[EMAIL PROTECTED]> wrote: What version of MySQL do you have? Depending on that, there are different methods. MySQL 5, can you please show me how to do it.. Did you look at the link David sent you? http://dev.mysql.com/doc/refman/5.0/en/date-and-t

Re: Table analysis - Help required urgently

Ratheesh K J wrote: Hello all, I am required to analyze all the tables of our system. I need to know the key parameters that should be taken into consideration for analysis. I am not speaking about ANALYZE TABLE . I am required to manually look into all table structures and pin point problems

Re: version of mysql

Vittorio Zuccalà wrote: Hello, i'm installing an open source program based on mysql because i want to try it. It controls mysql's version and it wants 4.1.16 but i've 4.1.11... Is it possible telling to mysql server to give a different number of its version? In other words: is there some co

Re: building mysql-5.0.22 from src

bruce wrote: hi... i have FC3/4 systems. i'm going to need to have mysql-5.0.22, but there appears to be numerous dependencies that yum can't easily resolve from the RPM i've found. can i reasonably easily build mysql-5.0.22 from src for my FC3/4 systems... the systems are pretty much standard

Re: Multiple-Column Indexes Question

Arias Gonzalez, Javier wrote: Assuming we have the following table: CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), ); With last_name having 1,000 different values and first_name having 1000,

Re: building mysql-5.0.22 from src

bruce wrote: hi chris... i tried that approach with MySQL-5.0.22 i did the rpmbuild -- ... and got the rpms for mysql. when i tried to install the mysql server rpm, it kicked out the dependency error regading the libmysqlclient.so.14... other apps require this version apparently. i would

Re: where may I find sqlplus.hh?

nayak_ratnadeep wrote: Hello, I have downloaded a code on c++ to talk to MySQL database from net which included a file "sqlplus.hh" , where may I find the file?Please help me soon. Why have you posted this 5 times? Did you look at the mysql website at ALL? It took me 30 seconds to find i

Re: where may I find sqlplus.hh?

nayak_ratnadeep wrote: I found in net but can't get the file. please help me.. Always reply to the list. Here's a hint: look for "C++ API" in the documentation. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROT

Re: Calculating birthdays and distances... Is there a bug?

Daevid Vincent wrote: I posted this as a comment on the page too, but I’m curious as to why the top solution is off by a day or so... Is this a bug or a rounding issue or what? Is there a way to "fix" the top one to work the way I expect/want it to work? I suspect it's because (as Jack Palance sa

Re: maximum files size for longblob - what is bigger?

[EMAIL PROTECTED] wrote: what us the maximum filesize for longblobs in kb? Is there anything bigger? That's answered in the documentation - http://dev.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTEC

Re: Delete one record, automatically delete other records...

Chris W. Parker wrote: Hello, I'm not sure if this is possible (or what it's called, and how to search for it) at the db layer or if this has to be done at the application layer... I would like to be able to delete one record in one table and then automatically (without making an ext

Re: Transactions and testing an Insert statement

Chris W. Parker wrote: Hello, Me again. Excuse for sending two questions so closely together. I'm looking through the MySQL manual (as well as searching Google and the PHP site's MySQL functions) trying to find out how to test an Insert statement (or any other statement for t

Re: mysqld won't start

Charlie Stanton wrote: From: root To: mysql@lists.mysql.com Subject: mysqld won't start >Description: In an attempt to upgrade to MySQL 5.0.24 it was necessary to use "rpm -e ..." to remove version 5.0.22 then "rpm -U ..." to install the shared,server and client modules. When I

Re: Trouble with using "IN" for a sub-query statement

Chris White wrote: On Monday 07 August 2006 12:13 pm, William DeMasi wrote: I meant to have it say : "select * from c2iedm_dev2.act where act_id IN (select obj_act_id from c2iedm_dev2.act_functl_assoc where subj_act_id =24);" What does the output of (the subselect): select obj_a

Re: Redeclare Class error

John wrote: I get this error:Fatal error: Cannot redeclare class mysql in /home/public_html/n/config.php on line 6 This is a php question, it's not a mysql problem. Find a more appropriate mailing list/forum. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To uns

Re: Table size??

Ratheesh K J wrote: Helo all, Just wanted to know when should a Table be considered for partitioning ( or should it be archiving ). Almost all of our tables are of Innodb type. I am looking for an estimate rather than a "Depends on situation" kind of an answer. We have few of our table swhic

Re: Doubt on Index Merge??

Ratheesh K J wrote: Hello all, I have a doubt on Index Merge. We are currently using MySQL server 4.1.11. As a part of DB management I am currently doing an analysis on all the Tables. While looking into the table structures, their columns and Indexes I found that most of the tables have a

Re: delimiter in mysql client

Asif Lodhi wrote: Hi, I am replying back to my own post. Sorry, I forgot to mention that I am using mysql client on WinXP Pro SP2 and MySQL-5.0.22 is installed on the same machine. Any hints as to why mysql client is showing this behavior? The only thing you're changing is the delimiter? Ar

Re: SELECT duplicate set of rows

James Neff wrote: Greetings, I need help with a select statement that finds duplicate sets of rows. I have a table like so: batch_data --- ID - int (auto inc) Record ID - int DataValue - VarChar(99) DataType - int With a sample of data like this: 1 100 1122A 1 2

Re: inner outer joins

Brian E Boothe wrote: hi all Can someone provide a small project using inner and outter joins with querys thanks alot create table t1 (id int); create table t2 (id int); This will find everything that has an entry in both tables: select * from t1 inner join t2; This will find records that are

Re: structure of whole table...

Can the output of the DESCRIBE be stored somewhere ( in file)? You can store it where-ever you like. Mysql stores it in a binary file which you can't read (ie it's not like the my.cnf file). I suppose the DESCRIBE query doesn't return the result set. What are you after exactly? Using 'ex

Re: random sort?

Tanner Postert wrote: so if i have a list of records... id name other 1 water H2O 2 water aqua 3 water liquid so i want to select other where name = 'water' but i want to only return 1 result, and for that result to be a random value. is there a way to sort by rand() ? Yes. Funnily eno

Re: non-text data

Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif to appear as a gif and not text? In your connecting programming language. Mysql doesn't know or care whether it's a gif, pdf, word doc or

Re: problems with sql-mysql mode of emacs on Windows

Jieqi Wang wrote: I am using WinXP, GNU Emacs 23, MySQL 5. When I start a mysql process with `sql-mysql' in emacs, no output is displayed in the buffer until I quit or kill the process. After I add the following line into the initialization file of emacs, (setq sql-mysql-options '("-C" "-

Re: Changed?

MYSQL wrote: Hi, I recently updated to version 4.1.2 from a slightly older one like 4.1.11 or something. But I am now having a problem. Before,this query would work fine: Select distinctrow * from mytable order by mydatecolumn. mydatecolumn is a column of type date. This used to return t

Re: Random SELECT on subset

Mark wrote: Dear MySQL-ers, Using MySQL 4.1.20, I'm trying to do a complex query on a subset; well, complex to me, that is. :) In Perl, I'm trying to get 4 random entries from a subset "WHERE processed = '1'" and "columnId" is unique. Like so: $sth = $dbh->prepare ("SELECT columnId FROM queue

Re: Random SELECT on subset

Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 3:55 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset In Perl, I'm trying to get 4 random entries from a subset "WHERE processed = '1'

Re: Connection Help - Stupid Question, sorry to bother.

Chris W wrote: By default MySQL uses port 3306 so you need to be sure that port is open on the server, and not blocked by a firewall. You also need to be sure the user you are trying to login as can login remotely. In the MySQL user data base, there is a column for host which is the host

Re: Group by with an IF

Steffan A. Cline wrote: I have the following query: select *, if( season_week_date = "2006-08-16", "on", "off" ) as stat, sum(overall_points) as total_points from rosters r left join celebs c on c.celeb_id = r.celeb_id where season_id=5062 and user_id=1 group by r.celeb_id order by overall

Re: mysql++ problem(undefined symbol to)

ali asghar torabi parizy wrote: hello. i want to connect to mysql from c++ . i examine any version or platform. i use mysql5 and 4 in suse10 with gcc.then examine mingw and mysql5 and 4 and 3 for severeal times but i can't solve this problem.when i want to compile examples of mysql++

Re: Finding the last row that has

André Hänsel wrote: Hi, I have one table with images (image_id, name, filename, vote_count) and one table with votes (vote_id, image_id, vote_value, user_id). I want to find the image with the lowest vote_count that a known user has not yet voted. For MySQL 4.0 I have the following: SELECT i.*

Re: Random SELECT on subset

Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:18 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Then I want to select 4 random columnIDs, but only from the subset WHERE processed = '1' (so

Re: query needed

VenuGopal Papasani wrote: Hi, i got a table datavalue as follows code period value c1 20051 c2 20052 c32006 3

Re: non-text data

Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: On Mon, Aug 14, 2006 at 10:42:50AM +1000, Chris wrote: Dave Shariff Yadallee - System Administrator a.k.a. The Root of the Problem wrote: I use SELECT all from * ... and one row is a gif. How do I get that gif

Re: Random SELECT on subset

Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 4:54 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Hmm, this still does not do what I want: SELECT columnId FROM queue WHERE processed = '1' ORD

Re: Random SELECT on subset

Mark wrote: -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: maandag 14 augustus 2006 6:32 To: Mark Cc: mysql@lists.mysql.com Subject: Re: Random SELECT on subset Thanks to you, the query has been greatly simplified, but the result is still the same: every once in a

Re: non-text data

All right, so how would one set up a select where rows 1 and 2 are text and row 3 is a blob and for argument sake assume jpg for the moment. You want to select a row based on the content (image) of the jpg? Not possible unless you pass the whole blob into mysql so it can retrieve the rec

Re: table hangs on a large query

Randy Paries wrote: Hello, I have a table (see below) that has 111599 records in it. When i do a query like select * from gallery_object where dir = 'dirname' Use explain to see if it's using the index: explain select * from it could be that for that particular directory you have a lot o

Re: 1 to many relationship

Brian E Boothe wrote: how do i do 1 to many relationship?? i have this SQL syntax " i may need to add many issues per customer" so this is what i have Please help SELECT * FROM mcsdata.customers INNER JOIN mcsdata.issues ON (mcsdata.customers.id = mcsdata.issues.id

Re: Access mySQL database across Internet

Neil Tompkins wrote: The problem appears to be retrieving the data which is slow. Are you sure it's not the actual query that's slow? If you run the query in mysql (ie through the command prompt) is it fast? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsu

Re: 1 to many relationship

Peter Lauri wrote: [snip "Chris"] If you want multiple customers to be associated with each issue you need 3 tables: create table customers (customerid int auto_increment primary key, customername varchar(255)); create table issues (issueid int auto_increment primary key,

Re: 1 to many relationship

Peter Lauri wrote: [snip "Chris"] The 'where' clause cuts that down to only matching records between the tables. Without the where, you'd end up with lots of rows but with the where it will be fine. [/snip] Yes, it cuts it down to that number of records in the end

Re: problem with InnoDB

[EMAIL PROTECTED] wrote: Hi , select count(*) is painfully slow in case of innoDB when the number of records are around 1 million. Ths select count(*) query in myISAM takes 0.01 secs and the same query in InnoDB takes around 20.15 secs. Can anybody suggest me how to speed up this query ? Yo

Re: tmp table created using remote database - slow query

Rhys Johnson wrote: Hello I am running an online web shop using a MySQL (v5.0.19) backend. The web app runs really fast whilst the MySQL database is on the local machine (2x PIII procs, 2GB mem). However I tried moving the database onto a dedicated database machine (2x P4 Zeon 4Gb Mem Raid5) and

Re: tmp table created using remote database - slow query

Rhys Johnson wrote: Thanks for the reply Chris I seem to have the remote database working just as fast as the local one after comparing the my.cnf settings. Query times have dropped from 3.6s to 0.4s. However, now when I log in from a shell prompt remotely into mysql it takes about 10s for my

Re: problem with InnoDB

[EMAIL PROTECTED] wrote: Hi, There is a problem with CPU utlization when using INNODB. The CPU utilization goes to 100% in a dual processor solaris box. With the same setup, myISAM uses only 60% of the CPU. As I said before, stuff like 'count(*)' queries cannot use an index in innodb - if y

Re: 1 to many relationships

Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around "issues per customer" i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click "Add issue" the a form Comes up and i add the issue an

Re: MySQL - HHHHangs when a stored procedure is called - 5.0.22/InnoDB/WinXP-SP2

CREATE FUNCTION CharValIsNumeric (v VARCHAR(15)) RETURNS boolean BEGIN declare i, l int(2); set l=char_length(v); set i=1; while (i <= l) and (substring(v,i,1) in ('1','2','3','4','5','6','7','8','9','0')) do set i=i+1; end while; What if the string (v)

Re: Very long pauses on particular tables

Daniel Kasak wrote: Hi all. I discovered a very strange and painful problem on a customer's system last night. I have built a gtk2-perl GUI front-end for accessing data in a MySQL database. It works remarkably well on my laptop, and was working remarkably well on their system as well, but somet

Re: Very long pauses on particular tables

Daniel Kasak wrote: Chris wrote: I'd see if you can get logging enabled. I will do that next visit, yes. I was pushed for time - in between 2 other commitments, and wasn't expecting problems. When the pauses happen try a 'show process list;' and see what else mysql

Re: Very long pauses on particular tables

Daniel Kasak wrote: Chris wrote: Could be a simple network issue too.. are pings fast/slow? Pings are very fast. The reason I asked about that last one was some of the suggestions on this page: http://www.debuntu.org/2006/07/21/75-how-to-optimize-mysql-response-time/ OK. I've had a

Re: mysqladmin claims password in crontab !

Marc MENDEZ wrote: Hi, I work under Mandrake 10.1 First, I have a .my.cnf, which works quite well, since I can enter mysql without entering any password. Even mysqladmin works ! But, I have a batch process run by crontab as root, which tests if mysql is running (mysqladmin version --silent).

Re: Slow queries

Jon Molin wrote: Hi list I have 5 tables: words (word_id int auto_increment, word varbinary(40)) (has ~3.5M rows) with the keys: PRIMARY KEY (`word_id`),UNIQUE KEY `word_ind` (`word`) phrases (phrase_id int auto_increment, phrase varbinary(100)) (has ~11M rows) with the keys: PRIMARY KEY (`p

Re: mysqladmin claims password in crontab !

Marc MENDEZ wrote: Hi, For mysqladmin, this parameter does not exist. I'll try anyway by forcing password and user on the command line. I'll check tomorrow. Sure it does. $ mysqladmin --help | grep extra --defaults-extra-file=# Read this file after the global files are read -- MySQL Gener

Re: Slow queries

Unfortunately didn't that help, it leads to: ++-+---+---+--- | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+--- | 1 |

Re: 1 to many relationship

EXPLAIN is documented in the manual . So it is, but it doesn't show me the decisions it makes. It shows me whether it will use an index or not, it won't show me how it puts the whole thing together. Compare to the postgres one: http://w

Re: 1 to many relationship

Michael Stassen wrote: Chris wrote: > select * > from customers c, issues i, customer_issues ci > where c.customerid=ci.customerid > AND ci.issueid=i.issueid; Chris wrote: > That query should be the same as this one: > select * > from customers c > inner jo

Re: Log files

bruce wrote: hi... i changed the mysql my.cnf to provide log files. i restarted mysql, but i don't see the log files... any idea as to what i may have missed. i'm running FC4, mysql 4.1. the my.cnf is: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = max_connect

Re: Tables/sum

Peter South wrote: Ok here is what I'm trying to do. Table 1 Table 2 Table 3 Column1(id) Column2 (score) Column1(id) Column2(score)Column1(id) Column2(name) 1

  1   2   3   4   5   6   7   8   9   10   >