Re: User Variables

Does anyone else have any ideas about this topic? Thanks >>>Ed Reed <[EMAIL PROTECTED]> 04/23/05 1:29 pm >>> Thanks for the reply, I realize that user variables disappear when the connection closes but I don't understand why what I'm trying to accomplish can't be done. Doesn't it make since

Re: zip code search within x miles

On Mon, April 18, 2005 9:16 pm, Hank said: >> Let's say you've got, oh, 2000 records to search through. >> You're gonna end up doing a JOIN with: >> 2,000 X 65,000 == 130,000,000 tuples (records/results). >> 130 MILLION tuples is *way* too many for your basic $20/month site. > > I'd say take some e

Re: zip code search within x miles

On Tue, April 19, 2005 8:55 am, Hank said: > Talk about over complicating things... here's the above query simplifed. > > I can not figure out why they were self joining the table three times: > > SELECT b.zip_code, b.state, >(3956 * (2 * ASIN(SQRT( >POWER(SIN(((a.lat-b.lat)*0.01745

Re: zip code search within x miles

On Tue, April 19, 2005 11:55 am, Hank said: > Except that the zip code field is not (and should not be) numeric, so > the qualification test fails. *IF* your zip codes are all US zip 5-digit, and *IF* performance is really crucial, and *IF* you are 100% certain you'll never need non-US nor zip+4,

Re: LongText Warning

On Wed, April 20, 2005 3:20 pm, [EMAIL PROTECTED] said: > I have a table with a field that has a field of type LONGTEXT. I try to > insert a utf8 string with a length of 114544 and I get a warning that > text got truncated. According to the doc, the size of LONGTEXT is much > bigger than this. Any

Re: Table handler errors

On Sun, April 24, 2005 5:20 am, Mike Blezien said: > we started getting these table handler errors 12, not all the time, but > just now > then. this is a FreeBSD 4.9-RELEASE O/S, w/MySQL 4.0.24 installed. The > database > in question is used by this vBulletin BBS system. Not sure it's a problem > w

Re: joining six tables by mutual column

On Sun, April 24, 2005 3:51 pm, Schalk Neethling said: > Hope someone can give me some pointers here. I have six tables in the > database and I need to JOIN them on a row that appears in all of the > tables. How do I do this? I have so far done the normal 'cross-join' > saying SELECT * FROM table1,

Re: setting character sets "permanently"

Paul, DBI doesn't read the config file, DBD::mysql does. Erm, yep, I meant that, too :-) It's not expensive at all. Why do you think it's expensive? As the file must be read and parsed at each ->connect(...) invocation. It would be nicer if DBD could read the config file during the "use phase" (f

Re: setting character sets "permanently"

At 0:49 +0200 4/25/05, Fagyal Csongor wrote: Harald, Perhaps the following excerpt from "perldoc DBD::mysql" is relevant for you: mysql_read_default_file mysql_read_default_group These options can be used to read a config file like /etc/my.cnf or ~/.my.cnf. By default MySQL's C client

Re: InnoDB Writes blocking Reads

Heikki, I converted my tables to MyISAM and the exact same thing is happening. At this point I'm thinking it's more of a flaw w/ the MySQL server rather than any paticular storage engine given that in both MyISAM and InnoDB, all the queries are in the "NULL" state rather than "Locked". My tab

Re: Using InnoDB on 2 OSes

Marcin, - Original Message - From: "Marcin Lewandowski" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Sunday, April 24, 2005 4:36 AM Subject: Using InnoDB on 2 OSes Hi, I've got Win2k on one FAT32 partition, and I use MySQL 4.0.23 there. I've got gentoo linux on second pa

Re: Replication - is there a "server lag"?

Yes, replication can be behind. If you SHOW SLAVE STATUS on the slave computer, you can see exactly how far behind the slave is on replication. You should design your systems such that a lag in updates doesn't affect the logic of your system. There are many uses in which this is a non-issue.

Re: setting character sets "permanently"

Harald, Perhaps the following excerpt from "perldoc DBD::mysql" is relevant for you: mysql_read_default_file mysql_read_default_group These options can be used to read a config file like /etc/my.cnf or ~/.my.cnf. By default MySQL's C client library doesn't use any config files unl

joining six tables by mutual column

Greetings everyone. Hope someone can give me some pointers here. I have six tables in the database and I need to JOIN them on a row that appears in all of the tables. How do I do this? I have so far done the normal 'cross-join' saying SELECT * FROM table1, table2, table3, table4, table5, table6

Replication - is there a "server lag"?

Hi, I am new to replication so excuse me if my question is stupid. The manual recommends that a nice scenario to take advantage of replication in MySQL is to send all updating queries to the master server, and reading from the slave. I would like to use this setup (as usual, I have many more sel

Re: setting character sets "permanently"

In article <[EMAIL PROTECTED]>, Fagyal Csongor <[EMAIL PROTECTED]> writes: mysql> show variables like 'character%'; >> +--+ >> -+ >> | Variable_name| Value >> | >> +--+--

Re: setting character sets "permanently"

Hmmm, strange... From the mysql client I see: mysql> show variables like "%char%"; +--+-+ | Variable_name| Value | +

Re: setting character sets "permanently"

Mathias, 2 other things : 1. what is your character set when you install the mysql server ? I used the binaries from mysql.com. I think that has latin1/swedish as a default. But anyways, I really don't like the idea to recompile MySQL just to get my character sets work all right. 2. what cha

Re: setting character sets "permanently"

Mathias, And you can add all those variables to the ini file : character_set_client=latin2 character_set_connection=latin2 character_set_database=latin2 character_set_results=latin2 character_set_server=latin2 No, I can not. MySQL won't start. More precisely, "character_set_results" is not recog

RE: setting character sets "permanently"

2 other things : 1. what is your character set when you install the mysql server ? 2. what characater set you see with "show create database" ? Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: math

Re: setting character sets "permanently"

Mathias, [...] I changed my.ini (.my.cnf) like this : [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 log-bin = "C:/Program Files/MySQL/MySQL Server 4.1/Data/binlog" #Path to installation directory. All paths are usually resolved relative to this. basedir="C:/Program Files/MySQ

RE: setting character sets "permanently"

And you can add all those variables to the ini file : character_set_client=latin2 character_set_connection=latin2 character_set_database=latin2 character_set_results=latin2 character_set_server=latin2 Be sûr that OLD data in your database will not suffer (replication and binlog for example). Expor

RE: setting character sets "permanently"

Hi, Look at this : mysql> show variables like 'character%'; +--+ -+ | Variable_name| Value | +--+ -+ | character_set_client

setting character sets "permanently"

HI, I am using MySQL 4.1.11 on FC3, and I have trouble reading back latin2 characters. No wonder, as character_set_client, character_set_connection and character_set_results are all set to latin1. The problem is that I cannot set them to latin2 _permanently_, I mean every time I connect to the

Re: Why is this table lock?

Reto, InnoDB always does locking on the row level. The IS and IX table locks are 'intention locks'. They do not block anything, except full table lock requests (LOCK TABLES ... WRITE, for example). The main purpose of IX and IS is to show that someone is locking a row, or going to lock a row in

Re: InnoDB Writes blocking Reads

Andy, no locking problem can explain why queries to tables that are NOT being updated get stuck. And your SHOW PROCESSLIST or SHOW INNODB STATUS did not show any query waiting for a lock. This is not a locking problem. 8.65 reads/s, 17184 avg bytes/read, 1.62 writes/s, 0.81 fsyncs/s ... 9.08 rea

Re: crushed innodb table

iv, - Original Message - From: "iv" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, April 21, 2005 9:04 PM Subject: crushed innodb table hi I've got a problem with a crushed innodb table (as i think) When I'm trying to make a backup, something like this appears: m

RE: MYSQL to XML

Hi Mikel, There are a lot of possibilities including commercial (:o)) products. I suggest you those solutions. The output should be reparsed for your needs : 1. the -X on client : C:\Mysql>mysql -u mathias world -X -e "desc country" Code char(3)

RE: Performance issues when deleting and reading on large table

>> An index on 'gender' may have a cardinality of >> only two or three (male/female(/unknown)) for example. Never b-tree index such columns ! Oracle (db2 ...rdbms) has bitmap indexes which work fine fork such data. Look at BIN(myset+0) in http://dev.mysql.com/tech-resources/articles/mysql-set-data

RE: Multi condition/table select

Hi, Don't you miss some relationnal definition in your table. Personnaly, I suggest in lnk : Lnk: catid=int11 primary key prodid=int 11 The key being (caid,prodid). And simplier (Normal form) : Categories: id=int 11 primary key title = varchar Product

Re: Performance issues when deleting and reading on large table

> > It's a probably a case of not having the cardinality of indexes right and > thus making wrong decisions for queries. > - Currently there is not a single query in the application that does not use > the correct index. We only have key-reads. Wich would mean that MySQL is > creating these incorre

Multi condition/table select

Hi all I am trying to figure out how to do this in one query. Using MySQL 2.3.58 Tables (only relevant data shown) Categories: id=int 11 primary key title = varchar Products: id=int 11 primary key name=varchar Lnk: catid=int11 primary key lnk=int 11 (key

RE: Table handler errors

Hi all, Sounds like a max heap table size reached : mysql> show variables like '%heap%'; +-+--+ | Variable_name | Value| +-+--+ | max_heap_table_size | 16777216 | +-+--+ 1 row in set (0.00 sec) if you red

RE: Performance issues when deleting and reading on large table

If you have no active transactions and want a cron delete, an example is : * Create table tmp as select * from your_table where ... <- here indexes are used * drop indexes * delete from you_table where ... * insert into your_table select * from tmp * create index on you_table. You must test it to

RE: Performance issues when deleting and reading on large table

Hi Jigal, Mathias, Thanks the time you took to reply to my issue's! I would like to clear out some things. > It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. - Currently there is not a single query in the application that does n

RE: mysql_performance

Hi all, I can see a cartesian product on the EMP table. Is this really what you're looking for. There is no column joining T2 and T3 ! Also as Peter said, you should have a lack of indexes on your tables. If you can send me your .frm,.myd and .myi files of the two tables as a zip file, I may help

RE: Performance issues when deleting and reading on large table

Hi all, Know that indexes are good for select(s), but very bad for massive insert,update and delete. If you want to do a massive delete with a cron, it's better to : * select the rows to delete (using indexes) * delete indexes * delete rows (already marked) * recreate indexes Another way if

Re: mysql_performance

Moemen, You assign a string _position_ result from LOCATE to a SUBSTRNG _length_ argument. Is that what you mean? Do you have indexes on persons.item_id, emp (item_id, item_type, f2, f6, f7)? Once there are such indexes, try ordering the WHERE ... ANDs to correspond to those keys so the optimis

Re: Table handler errors

> Yes, I've going through the docs and the manual i have hear locally, couldn't > really find a specific reason and/or causing for the error. The table type that > was causing the error was a HEAP table, changed it to a MyISAM table type and > the error disappeared, no more handler errors now... go

Re: Performance issues when deleting and reading on large table

From: "Almar van Pel" > After some time (sometimes a week sometimes a month) it appears that the > index of the table gets stuck. > It tries to read from the table but does not get response. This causes the > connectionqueue to fill up > and the load on the system increases dramatically. In other

Re: Table handler errors

Jigal van Hemert wrote: From: "Mike Blezien" we started getting these table handler errors 12, not all the time, but just now For errors from the operating system you can use perror to find out the description of the code: http://dev.mysql.com/doc/mysql/en/perror.html Regards, Jigal. Yes, I've goi

Re: Table handler errors

From: "Mike Blezien" > we started getting these table handler errors 12, not all the time, but just now For errors from the operating system you can use perror to find out the description of the code: http://dev.mysql.com/doc/mysql/en/perror.html Regards, Jigal. -- MySQL General Mailing List F

Re: InnoDB Tablespace per Schema?

From: "Andreas Schildbach" > Is it possible to configure MySQL so that it uses one InnoDB tablespace > file per database schema, instead of mixing all data into the shared > tablespace? No, but you can use a file per table: http://dev.mysql.com/doc/mysql/en/multiple-tablespaces.html It still uses

InnoDB Tablespace per Schema?

Hello everyone, Is it possible to configure MySQL so that it uses one InnoDB tablespace file per database schema, instead of mixing all data into the shared tablespace? I'd like to keep my applications data seperate, and having one file per application (schema) should make data migrations easier.

Table handler errors

Hello, we started getting these table handler errors 12, not all the time, but just now then. this is a FreeBSD 4.9-RELEASE O/S, w/MySQL 4.0.24 installed. The database in question is used by this vBulletin BBS system. Not sure it's a problem with the BBS or how MySQL is configured. Was wonder if

bulk_insert_buffer_size

Dear all, I read the following on this page : > bulk_insert_buffer_size > > MyISAM uses a special tree-like cache to make bulk inserts faster for > INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA > INFILE. This

Performance issues when deleting and reading on large table

Hi, I'm managing a large free website running on Linux, with MySQL 4.0.23 and has 2Gb memory. (PIV 2,6 Ghz) MySQL is configured to have 382M key-buffer. There are two problems I have, wich have to do with a large table. This table contains 5 million records and is the core of our application.

mysql_performance

Dear all, I have a problem running this query "select distinct T1.item_id, T1.f2 from Persons as T1 , Emp as T2 , Emp as T3 where T1.item_type='6.' and T2.item_type='6.1.9.' and T3.item_type='6.1.' and T2.f2 like '1.1.16.%' and substring(T2.item_id,1,LOCATE('.',T2.item_id))=T1.item_id a