Re: Getting around 2 queries
Matt Chatterley wrote: One option would be to 'union' the two queries (assuming the columns are the same type and length), allowing you to run one query string: Select serial from blacklist where serial = x Union Select serial from seriallist where serial = x Would return 1 or 2 rows, depending on whether rows are found in one table or both. You wouldn't know which table though (but from your message, I guess that is unimportant). If you needed to know which table it came from, you could just expand this query a little: Select serial,'blacklist ' as Tablename from blacklist where serial = x Union Select serial,'seriallist' as Tablename from seriallist where serial = x -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table is Read Only
Smartyone wrote: The database used to work, but then something must have broke, because now it is suddenly Read-Only (reports Read Only when I try to add a record using MySQLCC). Make sure the owner and group of the database files is set to mysql. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help
I've got a query that I can't seem to get optimized, so I'm hoping someone here can spot something I've missing! Table has three columns: CoordID int unsigned, Zip_Lo char(9), Zip_Hi char(9) Table has 3 million records indexes: acg_lo (Zip_Lo) acg_hi (Zip_Hi) acg_combined (Zip_Lo, Zip_Hi) Here's the query: select * from acg where zip4_lo_pot = '80128' and zip4_hi_pot = '80128' Explain shows: type: ALL possible keys: acg_lo,acg_hi,acg_combined rows: 3022309 extra: Using where So, how can I optimize this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Lost 3 Weeks of Data
Schmuck, Michael wrote: I've got a big problem. My MySQL server has yesterday lost data since 20th january. Yesterday at about 14 o'clock we resartet the demon on our bsd server since september 03. I belive the deamon didn't wrote the data into the files. At the restart of the database he loaded the old files and lost erverything newer then 20th. What do you mean: he loaded the old files and lost everything newer than 20th. Did he restore from a backup tape? If so, then it's probably a problem with your backup system not properly backing up the mysql tables. While the Mysql process is running, the files are in-use by Mysqld, and many tape-backup programs cannot properly copy files that are in-use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple question : Find older CHILD for each PARENT
Benjamin PERNOT wrote: I want to get a list of all the parents with the age and the name of the older child they've got. Let's say that a parent can't have 2 children with the same age. I can solve my problem by using multiple queries but that's not very clean and a bit heavy (especially if there are lots of parents). This is a perfect example of a max-concat query. Look up max concat on the mysql website, and it should show you a similar problem with solution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow query times
Balazs Rauznitz wrote: However when the 'sex' column is involved: mysql select count(*) from sex where id459000 and id =46 and sex = 'M'; +--+ | count(*) | +--+ | 504 | +--+ 1 row in set (5.09 sec) Any way to make this faster ? Well, MySql can only use 1 index per table to optimize a query. It's apparently using the index on ID, so it then needs to examine all records in the right ID range to see if they meet the sex='M' condition. You could build an index on both fields as one index, and MySql should be able to use it to resolve both parts of the query. create index id_and_sex_index on sex (id,sex); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient SQL Statement
Roger Baklund wrote: Either way, I was surprised to see the like to be in the top performers and left() to be last. I suppose the LIKE operator is optimized for the case when it begins with a constant: mysql select BENCHMARK(1000, 'dfsfsdfs' like '%F%' ); 1 row in set (3.43 sec) MySql will use indexes to optimize LIKE queries if the expression does not start with a wildcard character. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Authentication screw up
Nicholas Fitzgerald wrote: go to the command prompt and use mysql or mysqladmin I get access deniged for user @localhost. No root or anything like that. I don't know where I screwed up, but I obviously screwed up something. Any ideas out there on how I can get back in control of this situation? Shutdown mysqld, and restart mysqld with the --skip-grant-tables option. Now you will be able to connect as root without specifying a password. Once you've connected, give the command flush privileges to make Mysql start using the grant tables. Then you can add or edit records in the mysql.user table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compiling from source
Christopher L. Everett wrote: I'm using AMD K7 servers and don't need/want InnoDB so I want to compile mysql from source and I was curious about what compiler flags to use, so Without recompiling binaries, you can add skip-innodb skip-bdb to your my.cnf file. 2. What compiler flags do the binaries distributed on www.mysql.com use? http://www.mysql.com/doc/en/MySQL_binaries.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Method for Learning mysql
Marc Dver wrote: Based on the collective experiences of the members of this group, what are the best methods for learning mysql, both from the perspective of I attended the week-long Mysql training course, and it was excellent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a query..
Tibby wrote: ..and I want to get this with a single query: +-++--+ | key | desc| value | +-++--+ | 2 | book| 7 | | 6 | pen | 7 | +-++--+ I need to get only one row from col. DESC, the one with the highest VALUE. With one query... select `desc`, max(value) from mytable group by `desc` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Documentation bug?
Chris Nolan wrote: This seems a bit confusing. On one hand, it says that updates don't fail, but on the other hand it says they are stalled until ALTER TABLE is done executing. Am I going blind/loosing my mind (a possibility I am open to) or do others agree with me? It looks perfectly correct. They are stalled, in other words, the queries will be forced to wait. They won't fail, they will simply not complete until the ALTER TABLE is done. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: customizing order by question
Brandyn Riffle wrote: What I'm trying to do is sort by a column with by pre-set criteria; I've a political database with events with columns for the year, month, day, and event. I'd like to order by months, (e.g. JAN, FEB, MAR, etc...) after sorting by year. The sorting by year part was easy... the months are another matter. First, the correct solution would probably be to store your event date in an actual DateTime column. Then MySql would know how to sort it properly. And you could still get the seperate pieces out easily. For example if you had a EventDate column of type DateTime, then you could do select year(EventDate) as Year, monthname(EventDate) as month, dayofmonth( EventDate) as Day, dayofweek(EventDate) as WeekDay from MyTable and you'd get back columns like: |year|month |day|WeekDay| |2003|December| 12|Friday | -- So, that would be the RIGHT way to do it. But, you can make do with what you've got as well. I'm assuming you've got a Month field that contains 3 letter month abbreviations like JAN,FEB,MAR,APR,MAY, etc. Adjust the actual abbreviation/spelling as needed: select case month when 'JAN' then 1 when 'FEB' then 2 when 'MAR' then 3 when 'APR' then 4 when 'MAY' then 5 when 'JUN' then 6 when 'JUL' then 7 when 'AUG' then 8 when 'SEP' then 9 when 'OCT' then 10 when 'NOV' then 11 when 'DEC' then 12 else 13 end as MonthNum, month,day,year from test2 order by year,monthnum The case statement here converts the month abbrev. into numbers which will sort correctly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Login Problems on 4.1
At 14:42 -0400 12/12/03, Victor Medina wrote: It happens that when a client tries to log into the db server using passwords the server doesn't seems to authorize. Even the most recent MyCC client fails to autorize users using passwords. Do I need to compile the clients against the new server's library? The MyCC client was linked againts the 4.0.16 server. was there any change in the auth-protocol? 4.1 changed the authentication code. You can tell mysqld to use the old authentication by starting it with the --old-passwords option (or add old-passwords to the [mysqld] group in my.cnf). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key is not used
Corin Langosch wrote: Hello, thanks for your fast reply. even when i use EXPLAIN SELECT * FROM `actions` ORDER BY datum LIMIT 10 the key isn't used. the query takes about 2s :-( What does it show if you do a show indexes from actions --- If you are sure the optimizer is wrong, you can FORCE it to use an index. The USE INDEX option only suggests an index to mysql. SELECT * FROM actions FORCE INDEX (datum) ORDER BY datum And see if it makes a speed difference. Most often, if MySql didn't use it, then it won't help but I'm sure it can be wrong once in a while. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Maximum query size
Mark wrote: Is there an easy way to determine the largest sql query I can pass between a perl/C app to my MySQL database? It seems to wig out around the 1 meg range but without resorting to trial and error I'm not sure how to get an exact figure. the max size would be limited by the variable max_allowed_packet so, if you do a show variables like 'max_allowed_packet' it will show you the limit. By default, it is set to 1047552 bytes. If you want to increase that, add a line to the server's my.cnf file, in the [mysqld] section : set-variable = max_allowed_packet=2M and restart mysql server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
Time for first SQL : 21 sec.
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna'IN BOOLEAN MODE) AND
MATCH (cds.title)AGAINST ('music mix 2001'IN BOOLEAN MODE)
In this case, it cannot resolve the query JUST using indexes.
After finding all records in the index where artists.name matches
madonna and title contains all the words music, mix, 2001,
then it must retrieve each record, and examine the title field to
see if the three words are found together in the phrase.
In your other example, it only needs to use the fulltext indexes
to know which records satisfy your query, resulting in MUCH
faster query time.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed difference between boolean full-text searches and full-text searches
Uros Kotnik wrote:
It makes sense, but Sergei G. said :
And are you sure the numbers are correct, the first query - the one
without IN BOOLEAN MODE - is faster ? I would expect the opposite.
I guess that for my DB I can't expect satisfied in boolena mode times
?
But also when searching without in boolean mode and include search
criteria from TRACKS table, 13,841,930 rows , like AND MATCH (
tracks.title) AGAINST ('remix')
I get ~10 sec. times.
Am I doing something wrong or this results are correct for this amount
of data, I would be satisfied with 0.5 - 1 sec. times
If I'm not mistaken, IN BOOLEAN MODE simply changes the parser
logic. It tells MySql to process the special characters, like
+-*. I don't think it's the IN BOOLEAN MODE that is causing
the slow query, but the fact that you are looking for the phrase.
If you were to do
SELECT artists.name, cds.title, tracks.title FROM artists, cds, tracks
WHERE artists.artistid = cds.artistid AND artists.artistid =
tracks.artistid AND cds.cdid = tracks.cdid AND MATCH (artists.name)
AGAINST ('madonna' IN BOOLEAN MODE) AND
MATCH (cds.title) AGAINST ('+music +mix +2001'IN BOOLEAN MODE)
Then you'd probably still get the fast search time, since the query
simply requires all three words. MySql can resolve this just using
the index.
In your example, the BOOLEAN MODE for
MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
isn't doing anything special, since you aren't using any
special chars to modify the search expression.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Licence question
Yves Goergen wrote: (1) I want to start a (small, non-free) webhosting service and offer webspace with PHP support and a MySQL database account. There will be some standard tariffs that include a database account but I'm going to make them available as extra upgrade, too, for a monthly fee. Do I need a MySQL license for this use? I guess no, but I'm not really sure. I do not think so. You can download and install a GPL copy of MySql, and let anyone you want use it. You are not shipping or distributing any part of MySql. Your customers are simply users of your server. (2) If I'm planning to choose MySQL as DBMS for future software I code in my one-man-company, instead of MS Access, I'd need to compile the MySQL client libraries into my application. Another way could be the ODBC interface, but If the software was written for use BY your one-man-company, than no. But if you are selling this software to a client, then yes, they would need a commercial license. The license is per server, so either you or your client could purchase the license. If your client was going to install MySQL on two seperate servers, then two licenses would need to be purchased. If your client already had a commercial MySql license, then they wouldn't need to purchase another license to use your app. for this use? (Of course, they pay for my programs.) I guess yes, but what in detail? Me for the client libraries? I haven't found pricing information Typically, MySQL AB doesn't sell a license for the client libraries. The commercial license is a complete package, server and client. If your intention is to ship a functional MySql app, and just have the client download the GPL server, you would just need to purchase the commercial server. Having the client download/install the server is basically just an attempt at getting around the commercial license. But, early in this thread, the situation where someone might ship an application that could optionally connect up to many different databases. MySql isn't required for the app to run, but the app COULD connect to MySql if it was there. That is a case where a specific CLIENT license might be applicable.On the MySql site, on the Pricing page, it says: MySQL Client Prices For circumstances where a MySQL client license is required, please contact us for a quote. So maybe they can deal with that scenario. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large data set load and access
[EMAIL PROTECTED] wrote: Thanks for the heads up on this. Unfortunately the only varchar is the gbl_locus field, so I'm not sure how much this would by me for the space. Thanks again, Brad Eacker ([EMAIL PROTECTED]) No, it wouldn't save you any space. It would make your file a little bigger on the drive. varchar is a variable length field. As you've got it now, if you store a 10 byte string in that field, it'll only take up something like 14 bytes. If you store 1 character, it'll take up 5 bytes. So you are currently saving a little space. BUT! Variable length fields slow Mysql down a little bit when it's reading the table, since Mysql needs to read in each record completely to get to the next record. If you have no variable length fields, then mysql can calculate right away the starting position of any record based on the fixed record size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large data set load and access
[EMAIL PROTECTED] wrote: mysql create table gb_locus ( - gbl_id int primary key, - gbl_fileID int, - gbl_locus varchar(20), - gbl_sizeint, - gbl_datedate, - gbl_phylum char(3), - gbl_foffset int - ); At a recent MySql class, I learned that using all fixed length fields instead of variable length fields can improve speed. This is because MySql can skip thru records faster if the records are all a fixed length. So, if you were willing to give up a little storage space, make the gbl_locus field a Char(20) instead of a varchar(20) and see if it speeds things up. I found noticable speed increase in my selects doing this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Licence question
Stéphane Bischoff wrote: We are programming a Delphi application that interacts with the MySQL server from Windows. Normally we would need a client side licence ? But if we use a set of components (from a third party) that allow us to interact with the MySQL server without using the MySQL client. In this case, do we need to buy a client licence at all ? This is your standard I am not a lawyer type answer, because reading the text of the GPL can be overwhelming, but the way I understand it, if you are shipping MySql with your app, then you've either got to release your app under the GPL, or you've got to buy a commercial Mysql license for each copy of your app that you ship. If you were to simply download and install MySQL at your company office, then write apps for in-house use at your company, then you have no license issues. Your apps would not need to be GPL, and you do not need a Mysql commercial license. This was discussed by a Mysql AB employee during the MySQL training class I took a few weeks ago. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Linux access question
Noamn wrote: About a year ago, I set up mySQL on a computer running linux (probably RH7.1). At the time I thought that I would investigate how I could use this server in my business, but then I had more pressing issues and so neglected the subject. Now that I have some spare times and correct tools (Delphi 7), I want to renew my acquaintance with mySQL. I am trying to access the server from two computers running Win98. Both of them can connect to the linux machine via telnet and run programs such as the command line 'mysql', but only one of them allows me to connect via windows programs (I am using a program imaginatively called 'mysql_test.exe'). Of course, the machine which doesn't connect is the machine on which I am doing the program development. First, you need to install the Mysql windows client on any client machine. It's probably installed on one machine and not the other. Then take a look at the permissions issues discussed by Victoria in her response. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
rubn ruvalcaba wrote: I want to know how could solve the next replication scenario: I have a master. I have 5 slaves. At start the slaves has a master snapshot. Now imagine slave 1, inserts a record. When it gets connected to the lan, it must replicate it's changes to the master. No, a slave receives changes that occur at the master. That's why it's a slave. I suspect you want each machine to be a Master and a slave. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql won't start
Michael Burke wrote: I'm using mysql on redhat 9 and wanted to enable the query log file.I copied one of the example my.cnf files to /etc and added an entry: --log to enable the log file but when I boot red hat I get a message that If you are specifying the option on the command line, then you need the -- But in the my.cnf, just add a line that says log or log=/path/and/filename if you want to specify the path and filename for the log. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: read: Off subject regarding the mysql list!
karl james wrote: Hey guys, (off Subject) I want all emails that come from mysql list to go into a mysql folder that I have setup for it, but no matter what rule I choose in Microsoft outlook it doesn't work. filter on the TO address. The list does not re-write the FROM address. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql won't start
Michael Burke wrote: InnoDB: See http://www.innodb.com/ibman.html for installation help. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. 031201 20:00:49 mysqld ended It seems strange since the only thing I've done is copy my.cnf to /etc. Did you just now create the my.cnf? My guess is that your data isn't where mysql is looking. In the [mysqld] section, there is an entry that says datadir=/data/directory -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Server Overload
Nev wrote: We have just upgraded to a Pentium 4 - 3ghz , with 1 gb ram because our last server was very slow. What operating system? Another related point, in the MySQL documentation it says in the mysqladmin show extended status that the Select_full_join value should be zero mine is very high, what does this mean? It means you don't have your tables properly indexed. When you do a join, ideally MySQL should be able to join the tables using indexed fields. Instead, Mysql has to walk thru your entire table to find the records for the join. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database replication
Richard Bewley wrote: Ok, but the slave would also replicate to the master? Is anyone using this type of setup? Both machines would be set up as both masters and slaves. We tried it during the Mysql class I took a few weeks ago. Worked fine, except for when there were conflicting updates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disorder result with ORDER BY with ENUM, INT
Kriengkrai J. wrote: -- System: MySQL 4.0.13, 4.0.16 on Linux x86 -- Table type: MyISAM, InnoDB -- Description / How-To-Repeat: -- 1. When I use -- SELECT id, type FROM test.report ORDER BY type, id; -- the result is in wrong order -- -- -- ++-+ -- | id | type| -- ++-+ -- | 4 | general | -- | 3 | general | -- | 1 | general | -- | 2 | general | -- | 5 | inhouse | -- | 6 | inhouse | -- .. It is sorted EXACTLY as you specified. First by type, and then by ID. If you want it sorted first by ID, then do ORDER BY id, type; But, if your ID field is unique, then adding ,type doesn't do ANYTHING. -- 2. But when I use -- SELECT id, type FROM test.report ORDER BY type AND id; -- the result is in right order -- -- -- ++-+ -- | id | type| -- ++-+ -- | 1 | general | -- | 2 | general | -- | 3 | general | -- | 4 | general | -- | 5 | inhouse | -- | 6 | inhouse | -- .. The AND between the two fields is doing a BINARY AND on the two values. It is meaningless in this case. 1 AND general = 0 2 AND general = 0 5 AND inhouse = 0 So the system is just giving them to you in the natural order, as if you didn't have an order by clause. -- 3. After running statement(2) I rerun statement(1) again and the result is then in order as (2). you probably reversed the field order when you re-ran statement 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which Linux....
I'm building up a new MySQL server. The box specs are: 2U, Dual 3Ghz Xeon processors, 6 Gigs ram, 500 gigs of Raid 5 storage. I'll have the hardware in my hands tomorrow. I've used RedHat on all my previous linux servers. I'm planning on going with Red Hat Enterprise Linux ES. But, I've heard that the new threading code in the latest linux kernal (2.6) really improves MySQL performance. The RedHat site says that Enterprise is based on the 2.4 Kernal, with numerous additions from the Linux 2.5/2.6 kernal. And I just spotted another place where it mentions Native Posix Thread Library: A new high-performance multi-threading capability provides improved performance for multi-threaded applications. So, am I assuming correctly that RedHat Enterprise does offer the new threading code? Anyone know any problems using the new RedHat Enterprise for MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
