Re: join speed vs. 2 queries

>I have a gut feeling that this kind of join should be able to be >done with similar speed without having to use a temp table Yep but remember the query engine uses one index per table so without seeing your EXPLAIN output I'd try indexing ... the data table on name_id,value_id,campaign_id, t

How to do a simple fulltext match? Thanks!

Hi! I am having a problem searching a fulltext field. I'm setting up a little code library program for a few of us who work together. One field (TEXT) is "keywords" and it is indexed as well as fulltext. I send a simple query via my form to the code below. The connection is successfully mad

Re: select where multiple joined records match

Guarded exclamations of success! This seems to be working for me so far - thank you! Here's an actual example of a working query on my database; the field names are a little different (sorry, I was trying to make my earlier example more readable). The main change, though, is that I did plai

Re: queries slower on InnoDB

min() and max() can use indexes so they shouldn't be slower. On Mon, 14 Feb 2005 17:13:07 -0700, Ryan McCullough <[EMAIL PROTECTED]> wrote: > what about other functions like doing a min() or max()? are those slow > limitations of innodb tables as well? > > > On Mon, 14 Feb 2005 15:52:21 -0600,

Re: Empty 'Relay_Master_Log_File'?

On Mon, 14 Feb 2005, Eric Bergen wrote: > My understanding is that relay-info.log stores Exec_Master_Log_Pos so > in order to only use master.info in a snapshot you need to make sure > that the slave is caught up when the snapshot is taken. It's common > for the I/O thread (controls master.info a

Re: Empty 'Relay_Master_Log_File'?

My understanding is that relay-info.log stores Exec_Master_Log_Pos so in order to only use master.info in a snapshot you need to make sure that the slave is caught up when the snapshot is taken. It's common for the I/O thread (controls master.info and downloads logs files) to be a few queries ahead

Re: queries slower on InnoDB

what about other functions like doing a min() or max()? are those slow limitations of innodb tables as well? On Mon, 14 Feb 2005 15:52:21 -0600, Eric Bergen <[EMAIL PROTECTED]> wrote: > Total row count is cached in the header for MyISAM tables. InnoDB has > no such mechanism for this because tran

Re: Empty 'Relay_Master_Log_File'?

On Mon, 14 Feb 2005, Eric Bergen wrote: > Did you copy the relay-log.info and relay logs from the seed slave? > > Relay_Master_Log_file is line 3 in the relay-log.info file. This file > is managed by the SQL thread and helps the slave keep track of what it > has execute in the relay logs. > http:

innodb error 995

Hi I'm running version MySQL 4.0.20a-debug windows server 2003 on a dedicated database server with an external SCSI RAID array with a 1 TB of disk space (over 400 GIG free) 1 gig of RAM basic settings key buffer 512 MB sort Buffer 32 Mb innof db Buffer Pool Size 512 MB my innodb datafile is abo

Re: Empty 'Relay_Master_Log_File'?

Did you copy the relay-log.info and relay logs from the seed slave? Relay_Master_Log_file is line 3 in the relay-log.info file. This file is managed by the SQL thread and helps the slave keep track of what it has execute in the relay logs. http://dev.mysql.com/doc/mysql/en/slave-logs.html -Eric

join speed vs. 2 queries

Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND

Re: Empty 'Relay_Master_Log_File'?

On Mon, 14 Feb 2005, Jeremy Cole wrote: > AFAIK, the log file name is not known by the slave unless it either > receives a rotate log event (go to next log) or you have started it > against a particular log file. If you start replication "from the > beginning" (not specifying a log file) against

Re: queries slower on InnoDB

Total row count is cached in the header for MyISAM tables. InnoDB has no such mechanism for this because transactions make it impossible to keep an exact row count. In order for InnoDB to get a row count it has to do a full scan inside a transaction which will take a long time. One way around this

"collate latin1_general_ci" Error

I just installed a new software package that included a MySQL upgrade. Everything was working fine until I tried to import one of my tables to my online database and got this error: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for th

Re: Empty 'Relay_Master_Log_File'?

Hi Atle, Yesterday I set up 4 new MySQL 4.0.22 replication slaves on 2 FreeBSD 4.10-R machines (via mysqld_multi). We have 10 other identical slaves and I followed the same procedure as always when seeding them. The new slaves were working fine, however when I issued SHOW SLAVE STATUS the Relay_Mas

Re: Time in VBA for Excel

Dan Wareham wrote: Hello, I have a table in my database with two fields set as Time types. The issue I seem to have is when I use an ADO connection in VBA for Excel to select the two fields. Instead of the expected format HH:MM:SS held within my recordset, I get the current date in the format DD

Empty 'Relay_Master_Log_File'?

Yesterday I set up 4 new MySQL 4.0.22 replication slaves on 2 FreeBSD 4.10-R machines (via mysqld_multi). We have 10 other identical slaves and I followed the same procedure as always when seeding them. The new slaves were working fine, however when I issued SHOW SLAVE STATUS the Relay_Master_Log_

Re: select where multiple joined records match

Hi, Thanks, but unless I'm missing something, that will return the same as SUBJECT="English" and (GRADE="1" or GRADE="2"), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? How about this: SELECT resources.id, resources.title FROM resources LEFT JOIN goals AS g

Re: select where multiple joined records match

Thanks, but unless I'm missing something, that will return the same as SUBJECT="English" and (GRADE="1" or GRADE="2"), which is resource records 1,2,3, and 4 - too many matches. Am I missing something? - AM On Mon, 14 Feb 2005 12:30:44 -0600, Gordon <[EMAIL PROTECTED]> wrote: Try this Select

Re: Replication issue: I/O thread dies immediately after START SLAVE with no logged errors

In response to Gleb Paharenko and Bruce Dembecki: Gleb Paharenko wrote: >Please send us an output of SHOW MASTER STATUS ans SHOW SLAVE STATUS. >Can you reproduce the problem using official binaries? I have included the output of those two commands below. I will try to reproduce the problem usin

RE: select where multiple joined records match

Try this Select * from resources, goals where resources.ID = goals.RESOURCE_ID and (SUBJECT="English" and GRADE="1") OR (SUBJECT="English" and GRADE="2"); -Original Message- From: AM Thomas [mailto:[EMAIL PROTECTED] Sent: Sunda

Re: show status - questions variable

At 11:00 -0500 2/14/05, Mayuran Yogarajah wrote: Questions: The number of queries that have been sent to the server. Is this the number of queries since the mysql installation, or the number of questions since the last reboot ? An easy test: If you restart the server, you'll see that the variable b

Re: Can I dissable transactions?

On Monday 14 February 2005 03:52 am, Ben Clewett wrote: > I am having a lot of problems deleting a large amount of data. Say 20GB > from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, > but bad hardware), then MySQL roles back the transaction, which takes as > many hours and s

Time in VBA for Excel

Hello, I have a table in my database with two fields set as Time types. The issue I seem to have is when I use an ADO connection in VBA for Excel to select the two fields. Instead of the expected format HH:MM:SS held within my recordset, I get the current date in the format DD/MM/. Then shou

queries slower on InnoDB

Hi, there, I am in the middle of replacing a MyISAM database with InnoDB. Queries "show table status" and "select count" are extremely slow which gave me some pain. I am wondering if there are any other queries on InnoDB that are significantly slower than those on MyISAM other than these t

show status - questions variable

Questions: The number of queries that have been sent to the server. Is this the number of queries since the mysql installation, or the number of questions since the last reboot ? thanks, M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.

Re: MySQL dump (OT?)

You could try setting up FileMaker to query MySQL directly, using ODBC. Otherwise, you can't create a FileMaker database directly, so you need to create an import format that FileMaker understands (tab, command, sylk, etc.). You would need to keep in mind that you may have embedded returns and/o

RE: select last row

If you have an auto_increment column, SELECT * FROM my_table ORDER BY auto_increment_col DESC LIMIT 1 If you don't have an auto_increment this might be a good time to add one. > -Original Message- > From: Mulley, Nikhil [mailto:[EMAIL PROTECTED] > Sent: Monday, February 14, 2005 08:15

Re: select last row

Mulley, Nikhil wrote: Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil Do you use a recno? If you have 4.1 you can use select * from table where recno = (se

select last row

Hi All, I have a table which is being continuosly updated, I just wanted to know how to output only the last row with the select statement. can anyone please tell me howto. thanks, Nikhil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists

Re: auto-increment stops at 127

"mel list_php" <[EMAIL PROTECTED]> wrote on 14/02/2005 13:54:35: > additional test, > it is always bugging at the key 127... > I put a backup online, with until 106. > Added few test records, from key 127 it just doesn't want to increment the > auto-increment field anymore. > I'm completly lost

Re: auto-increment field stops working

Hi, could you check to make absolutely sure that your taskId column isn't tinyint, which should explain it as it's max ( being signed ) is 127. /Johan mel list_php wrote: Hi list, I have a very strange (and worrying..!!!) problem with my tables. I'm running a 4.0.9 gamma (no choice for that) adn I

RE: auto-increment stops at 127

Change the column from a TINYINT (which has a maximum value of 127), see here: http://dev.mysql.com/doc/mysql/en/numeric-types.html HTH Mark Mark Leith Cool-Tools UK Limited http://www.cool-tools.co.uk -Original Message- From: mel list_php [mailto:[EMAIL PROTECTED] Sent: 14 February

auto-increment stops at 127

additional test, it is always bugging at the key 127... I put a backup online, with until 106. Added few test records, from key 127 it just doesn't want to increment the auto-increment field anymore. I'm completly lost here, any help would be greatly appreciated..

MySQL dump (OT?)

Hi all, This is perhaps off-topic, but I need to dump my MySQL database into a format that FileMaker Pro will understand. Does anyone have any tips for doing this? Thanks in advance, Erich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http:/

auto-increment field stops working

Hi list, I have a very strange (and worrying..!!!) problem with my tables. I'm running a 4.0.9 gamma (no choice for that) adn I had a MyIsam table with an auto-increment field. Everything worked fine friday, and today the "auto-increment" field is just not working anymore. When I want to insert a

Re: Query: Get 100 itemid's for each id.

> > >This is my table: > > > > > >CREATE TABLE items ( > > > id int(10) UNSIGNED NOT NULL default '0', > > > itemid int(10) UNSIGNED NOT NULL auto_increment, > > > PRIMARY KEY (`itemid`) > > >); > > > > > >I'd like to get 100 itemid's for each id. I guess I could solve my problem with a subsele

Re: Query: Get 100 itemid's for each id.

> >This is my table: > > > >CREATE TABLE items ( > > id int(10) UNSIGNED NOT NULL default '0', > > itemid int(10) UNSIGNED NOT NULL auto_increment, > > PRIMARY KEY (`itemid`) > >); > > > >I'd like to get 100 itemid's for each id. > > > >Is that possible. If so, please show me how. > - you have i

Re: 4.1.7 Character set problem ( Ithink?)

On 12 Feb 2005 at 14:09, Gleb Paharenko wrote: > Hello. > > Please tell us, what output the following statement produces: > SHOW VARIABLES LIKE '%char%'; Hi Gleb, mysql> SHOW VARIABLES LIKE '%char%'; +--++ | Variable_name| Value

Query: Get 100 itemid's for each id.

This is my table: CREATE TABLE items ( id int(10) UNSIGNED NOT NULL default '0', itemid int(10) UNSIGNED NOT NULL auto_increment, PRIMARY KEY (`id`) ); I'd like to get 100 itemid's for each id. Is that possible. If so, please show me how. Thanks, Jacob -- MySQL General Mailing List For

Re: 4.1.7 Character set problem ( Ithink?)

Hello. Please tell us, what output the following statement produces: SHOW VARIABLES LIKE '%char%'; You can use hexademical values for inserting the data. See: http://dev.mysql.com/doc/mysql/en/hexadecimal-values.html "Ian Gibbons" <[EMAIL PROTECTED]> wrote: > Hi List, > > I a

Re: instable behaviour of mysql

Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); Can you find, if your grants change? Can you reproduce a problem on the latest release (4.1.9 now)? schlubediwup <[EMAIL PROTECTED]> w

Re: mysqldump feature request

Hello. You can solve your problem using --all command line option (--create-options after 4.1.2) which is on by default as of MySQL 4.1. Chris Elsworth <[EMAIL PROTECTED]> wrote: > Hello, > > I have a feature request for mysqldump that would greatly ease a > current task I have;

Re: Convert to character set (upgrading from 4.0 to 4.1)

Hello. As said at: http://dev.mysql.com/doc/mysql/en/charset-conversion.html You should avoid trying to convert directly from latin1 to the real character set. If you have a backup, import a table from it. Check that your character_set_xxx variables have a corresponding values. See:

Re: My Problem.Help me

Hello. Why do you link both libmysqld and libmysqlclient with you file at once? "Mohsen Pahlevanzadeh" <[EMAIL PROTECTED]> wrote: > Dears,I have following Makefile : > INCS=-I/usr/include/mysql > LIBS=-L/usr/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm > LIBS_R=-L/usr/lib/mysql -lmys

Re: Why MySQL doesn't cache queries that populate temp tables?

Hello. As said at: http://dev.mysql.com/doc/mysql/en/query-cache-how.html A query will not be cached, if it uses TEMPORARY tables. "Homam S.A." <[EMAIL PROTECTED]> wrote: > Why MySQL insists on ignoring the query cache whenever > I use the same query repeatedly to populate a temp

Re: mysql ended problem

Hello. See: http://dev.mysql.com/doc/mysql/en/communication-errors.html >Sorry I missed to attach the error logs. >I could not infer from the log files. > >Below line is getting printed when I try to start MySql >050211 2:35:14 Aborted connection 12 to db: 'AMDB' user: 'root' host:

Re: increased disk ops after alter table

Hello. The problem could be related to the filesystem layer. You may use a raw disk partition or decrease the size of your tablespace (if it is autoextend). Pradeep Hodigere <[EMAIL PROTECTED]> wrote: > Hi All, > > I had to do an alter table on a very large InnoDB > table (100 milli

Re: more complexity (was: select where multiple joined records match)

In article <[EMAIL PROTECTED]>, "AM Thomas" <[EMAIL PROTECTED]> writes: > Now, if I understand how this is working: > SELECT r.TITLE > FROM resources r JOIN goals g ON (r.ID=g.RESOURCE_ID) > WHERE g.SUBJECT = 'English' > AND (g.GRADE = 1 OR g.GRADE = 2) > GROUP BY r.ID > HAV

Can I dissable transactions?

Dear MySQL, I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot:

mysqldump feature request

Hello, I have a feature request for mysqldump that would greatly ease a current task I have; in my replicated setup my master is currently MyISAM (as are most slaves), but I have one slave that is InnoDB for testing. Somehow or other, it's gotten itself out of sync, so I'm in the position of need