Re: Detect if table exists from within MySQL?
Ryan Stille wrote: If I have to, I could resort to doing another query in my application (SHOW TABLES) and seeing if my table was returned in that list. But I was hoping for a more elegant way to do it, within the single query. Maybe you could use SHOW TABLES LIKE 'your_table'; -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to match a binary null in a varchar column???
Richard F. Rebel wrote: do I say REGEXP BINARY what? I have tried \000 \0 as they are common representations for binary null. Have you tried WHERE your_column LIKE '%\0%'? That works for me. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: linux timestamp
Dotan Cohen wrote: Hi all, I have a field in a mysql database v4.0.18 that contains a linux timestamp. I have been googleing for a solution that would return to me all the entries where the timestamp falls on, say a wednesday, or between 2pm to 3pm. I am led to believe that it is possible, but I have found no examples. Something like: SELECT * from listings WHERE timestamp(day==wednesday) or SELECT * from listings WHERE timestamp(14:00 = time = 15:00) If you're wanting to do queries like that regularly, you should set up columns containing the weekday and the hour and index them. For a one-shot, you can use a query something like SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%W')) = 'Wednesday'; or SELECT * FROM listings WHERE DATE_FORMAT(FROM_UNIXTIME(your_timestamp),'%H')) = '14'; but it won't be fast if the table is big. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL and database size
Robert Crowell wrote: However, if the table was created with columns A, B, C, and D, most of the entries in the D column will be NULL. Is this considered 'good form'? Even though these entries are all NULL, they will still consume the disk space that a DOUBLE will, correct? I can't find it in the documentation now, but last I remember in MyISAM dynamic tables (which is what you'd have, assuming you're using VARCHARs) each record has a bit for each nullable column that indicates whether it's NULL or not (just as it has a bit indicating whether each column is 0 -- or the equivalent -- or not). So no, those NULL entries don't take any extra space. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unix timestamp
Scott Gifford wrote: SELECT COUNT(*) AS score FROM downloads WHERE dateline + 3600 = UNIX_TIMESTAMP() GROUP BY filename ORDER BY score DESC It would be better with WHERE dateline = UNIX_TIMESTAMP() - 3600 so that it can use an index on dateline. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index - max key length is 1024 bytes
javabuddy wrote: But still I can't get the part where my column size totals to 560, but MySql complaining that I have exceeded 1024. Did I went anywhere wrong??? Are you sure you've thought your index through correctly and considered how MySQL will use it? What sort of query would such an index be useful for? In most circumstance it makes little sense to index more than a prefix of a long VARCHAR or TEXT field, and indexing nine fields at once will only make sense if you use all nine in your query. Maybe you want a FULLTEXT index? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MAX on UNSIGNED INT Column
Jacob S. Barrett wrote: I have a column of type UNSIGNED INT which holds a 32bit counter. When the value of the field exceeds 2147483647 (signed max) the value of MAX on the column returns a negative number. Possibly this bug, fixed in 4.1.12? http://bugs.mysql.com/bug.php?id=9298 -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Shifting dates
Jigal van Hemert wrote: Maybe because dates before Jan 1, 1970 have an undefined timestamp and dates beyond 2038 cannot be used with 32-bit integers? Quite a few people were born before 1970 and sometimes one needs to store their date of birth too? Yes, but birthdates are generally DATE, not DATETIME, unless you're doing astrology. Sebastian was talking about DATETIME versus Unix timestamp INT. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seriously.. When are we going to get subqueries?!
I'm a little surprised that case-sensitivity is such a big deal. What sort of programmers randomly vary their capitalization from one occurrence of an identifier to the next, and wouldn't people who are so non-detail-oriented be making a lot of typos as well? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy
Jeff McKeon wrote: Am I right in assuming that while mysqlhotcopy is running, nobody else can write to or update the DB? Yes. That's why it's better to run it on a slave. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to get the name of the last failed FK constraint
Frank Schröder wrote: The thing that's really a headscratcher for me is why its possible for me to set a name for a constraint if it isn't displayed in an error and I can't get to it. It's useless. Same as setting a name for an index -- it allows you to alter or delete it later. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
Scott Klarenbach wrote: Can I select the maximum value across multiple columns? You want the GREATEST() function: http://dev.mysql.com/doc/mysql/en/comparison-operators.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select MAX(column1,column2)
[EMAIL PROTECTED] wrote: Hi all, what is max ? it's the first row when we sort data in descending order. so select col1,col2,col3,col4 ... from table order by concat(col1,col2,col3,col4 ... ) desc LIMIt 1; should be silar to what is needed. I say should :o) That would only work if the greatest values for col2, col3, col4, etc., all occurred in the same row with the greatest value for col1, and if all the values for col1 had the same number of digits (and the same for col2, col3, etc.). Consider this table: 10 2 3 5 4 8 1 12 7 Your query would give 5, 4, 8 (because 548 as a string is greater than 1023 or 1127), but he wants 10, 12, 8. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficiently finding a random record
Michael Stassen wrote: For example, if the selected random id is missing, we take the next id we find, like this: SELECT @rand_id:= CAST( 1 + MAX(id)*RAND() AS UNSIGNED) FROM history; SELECT * FROM history WHERE id = @rand_id LIMIT 1; That will have a possibly undesired effect. Records that have gaps in the IDs before them will be twice, three times, etc. (depending on the size of the gap), as likely to be selected as records with no preceding gaps. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: difficulty with UCASE and UPPER
Ed Reed wrote:
But if the user happens to put a numeric value within the text
somewhere then UCASE and UPPER both fail to convert the text to upper case.
Can you give an example? I think there's something else going on that you're
overlooking. UPPER() and UCASE() (which are synonyms for the same function)
uppercase all letters in the string. They don't care whether there are numbers
in it.
mysql select UPPER('abc123def');
++
| UPPER('abc123def') |
++
| ABC123DEF |
++
1 row in set (0.05 sec)
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Expression Confusion?
Tommy Barrios wrote: Using the below statement results in a null data dump: SELECT * FROM items WHERE item = '109S2' AND venturi_type = 'L-shaped' AND category = 'burner'; Whereas if change the 'S' in the item = 10902 like this: SELECT * FROM items WHERE item = '10902' AND venturi_type = 'L-shaped' AND category = 'burner'; I get a full complete data dump. Both numbers are legitimate part numbers in the item column yet one works the other does not. Apparently you have no rows where all three criteria are true. If you think you do, you need to examine the values in the table more carefully. Perhaps the item value has a newline at the end or a space at the beginning or something else not immediately visible. Try selecting LENGTH() or HEX() of a column to see whether it's what you expect. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave stuck at registering
I'm trying to set up replication over the Internet -- something I've done successfully many times before. But this time I'm having problems I haven't run into before, and I'm wondering if they're related to firewall settings or network problems or just something I'm overlooking. For the slave, I'm using the same settings that I used successfully for another slave of the same master at a different location. The relevant section of my.cnf looks like this: master-host = [the master hostname] master-user = [username] master-password = [password] server-id = 47 report-host = [the slave hostname] slave_compressed_protocol = 1 read-only When I start the slave I get the proper connected to master ... replication started in log 'FIRST' at position 4 message in the slave error log. In SHOW SLAVE STATUS I see Connecting to master briefly and then Registering slave on master. It stays in the Registering state for about 500 seconds, after which the I/O thread stops and I get Error on COM_REGISTER_SLAVE: 2013 'Lost connection to MySQL server during query' in the error log. In the error log on the master I get Aborted connection 114025 to db: 'unconnected' user: '[username]' host: `[slave IP]' (Got an error reading communication packets). If I use the mysql command-line client on the slave, I can connect fine to the master, and vice versa. Also, PHP and Perl programs on the slave use databases on the master with no problems. What would replication require that the normal client-server communication doesn't? The master is running 4.0.22 on FreeBSD and the slave is running 4.0.24 on Linux. Any suggestions? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave stuck at registering
Victor Pendleton wrote: Can you see the slave thread on the master when you do a show processlist from the master? Yes. It shows up with the command as Sleep, nothing in the State column, and NULL in the Info column. It hangs around for a while until Time reaches a little over 500, and then disappears. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave stuck at registering
Victor Pendleton wrote: From your previous posts I know you are very competent so no disrespect intended. Was the slave data reloaded from the master, and the master.info reset? What does the master.info log say? I copied over all the data from the master to the slave with rsync, did LOCK TABLES WITH READ LOCK and RESET MASTER on the master and rsynced again (all this while mysqld was not running on the slave). I checked the ownership and permissions, deleted the logs, and started MySQL on the slave. So the master.info is as created by the slave. It looks like this: [blank line] 4 [master hostname] [user] [password] 3306 60 -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IN giving me a fit
Scott Purcell wrote: I am in the docs trying to use the IN (13.1.8.3. Subqueries with ANY, IN, and SOME). Version: mysql Ver12.21 distrib 4.0.15 Win95/Win98(i32) Look at http://dev.mysql.com/doc/mysql/en/subqueries.html Subqueries aren't supported until 4.1. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index problem ?
Michael Gale wrote: When I run the following: `Select DISTINCT machine from syslog WHERE date1 (NOW() - INTERVAL 1 hour);' it takes 9min to complete. If I use Explain it says the query is using index hostname, should it not be using the index hostdate which contains fields machine and date1 since those are the fields I am using in my query ? Your WHERE clause needs an index on date1, which means an index that *starts* with date1. Your index on (machine, date1) won't help in this case. You could use an index on just date1 or on (date1, machine). The second would allow MySQL to do that query from the index alone, without referring to the data file, so it should be much faster than what you're doing now. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: zip code search within x miles
Hank wrote: 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: Also, the index on zip_code, latitude, and longitude doesn't make sense. Only the zip_code part of it was used, so it should have been on zip_code alone. Latitude and longitude were only used in calculations, so indexing them is useless. If additional conditions were added to the WHERE to limit the search to a square, then latitude and longitude indexes would be useful, but they would have to be separate from each other and from zip_code. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: GWAVA Sender Notification (Spam)
[EMAIL PROTECTED] wrote: This is what I call WAY OVER REACTING. *This member turned my email into the spam report immediately without thinking. Now I am recieving these.* I doubt that there's any connection. It's impossible to tell for sure, but the message was probably rejected because of the capital letters and multiple exclamation points in the subject line -- which means the person who posted the original complaining message probably got the same sort of bounce. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repairing Data packing with zeros
zzapper wrote:
ONE1- ONE0001
ABC23 - ABC0023
FGH123 - FGH0123
What Update Query should/could I have used?
Something like this perhaps?
UPDATE table_name SET propertyID = CONCAT(LEFT(propertyID, 3),
LPAD(SUBSTRING(propertyID, 4), 4, '0'))
WHERE propertyID REGEXP '^[A-Z]{3}[0-9]{1,3}$';
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
Ed Reed wrote: 01/01/05 SG Reviewed this 12/15/03 DSD Reviewed that 10/24/02 EWW Worked on that and tested this then stop to do something else 05/02/01 AW Did something 08/31/98 DSD Tested this 07/22/97 EWW Worked on that and did something I need a Select statement that returns the Date for the first occurance of the 'Tested this' substring That's the sort of thing you're going to be better off doing in Perl or PHP or whatever applicationn language you're using rather than trying to handle in your MySQL query. And of course if it's something you're doing regularly, rather than a one-time conversion, you should put that data into a proper table, with date, initials, and description as columns. If you're determined to do it, you'll need a bunch of applications of LOCATION() and SUBSTRING(), and probably IF(). -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
Dan Nelson wrote: How about: SELECT LEFT(description, 8) FROM mytable WHERE description LIKE %tested this% LIMIT 1 Hmm, I assumed he was talking about a multi-line VARCHAR, but now that I look again Dan's interpretation is probably the right one. My previous message doesn't apply (except for the bit about breaking it into columns if you're doing it regularly). -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a very tricky string extraction
Eamon Daly wrote:
SELECT
LEFT(SUBSTRING_INDEX(LEFT(log, LOCATE('\n', log, LOCATE('tested this',
log)) - 1), '\n', -1), 8)
FROM test WHERE log LIKE '%tested this%'
Another possibility:
SELECT SUBSTRING(log, LOCATE('\n', SUBSTRING(log, 1,
LOCATE('Tested this', log)-1))+1, 8)
FROM test WHERE LOCATE('Tested this', log);
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ROW_SIZE or something alike
Marco Neves wrote: I'm looking for some way to know the size each row of a table uses in my database (phisical - real disk space allocated or logical - datasize ignoring compression and any control data, don't mind, anything is better than nothing). You might try using the Data_length and Index_length values from SHOW TABLE STATUS, divided by the number of rows. http://dev.mysql.com/doc/mysql/en/show-table-status.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimising COUNT()
Stembridge, Michael wrote: I noticed another listmember used COUNT(fieldname) instead of COUNT(*). Is there a noticeable performance increase with COUNTing a column name instead of all columns? (ie, like SELECTing specific columns instead of using SELECT *) If anything, I'd expect the reverse, and the query result may be different, depending on your data. COUNT(*) returns the number of rows matching the WHERE clause, while COUNT(column_name) returns the number of matching rows where column_name is not NULL (so MySQL has to do more to calculate it). Note also that COUNT(*) without a WHERE clause is optimized with MyISAM tables to return the number of rows in the table very quickly. The same would not be true of COUNT(column_name). -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication errors
Irek Sonina wrote: I was thinking that master is executing the same queries that slave does - if an error exists on slave then it first comes up on the master and master is not executing it either. You prove that I was wrong... now I must check the integrity of the data on my slaves, which all are running with slave skip errors turned on :/. Anyone thinking of using slave-skip-errors should read the documentation about it, which should be enough to scare them away from that option (especially all): | You can (but should not) also use the very non-recommended | value of _all_ which ignores all error messages and keeps | barging along regardless of what happens. Needless to say, if | you use it, we make no promises regarding your data integrity. | Please do not complain if your data on the slave is not | anywhere close to what it is on the master in this case. You | have been warned. http://dev.mysql.com/doc/mysql/en/replication-options.html -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: skip-name-resolve
Mauricio Pellegrini wrote: Is there a way to check whether this option is active or not, while the server is running? Not sure why it doesn't show up in SHOW VARIABLES, but one way to check would be to see whether the hosts in the Host column of SHOW PROCESSLIST are shown as IP addresses or hostnames. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: safe way of replication?
Atle Veka wrote: Even if you replicate the 'mysql' DB, GRANT/REVOKE statements are not replicated, nor are FLUSH statements. So if you are adding new access privileges on the master they will not be active on the slave until you issue FLUSH PRIVILEGES (one the slave). What version are you talking about? GRANT and REVOKE seem to be replicated fine nowadays. I remember some bugs related to their replication, but they were about replicating them when they shouldn't be (when the mysql DB wasn't being replicated), not failing to replicate them when they should be. Also FLUSH PRIVILEGES is replicated as of version 4.1.1, according to the documentation. The original poster might want to look into the read-only option to prevent accidental modification of the slave data: | --read-only | | This option causes the slave to allow no updates except from | slave threads or from users with the SUPER privilege. This can | be useful to ensure that a slave server accepts no updates | from clients. | | This option is available as of MySQL 4.0.14. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Auto Escape characters
Scott Klarenbach wrote: Is there a flag in MYSQL to automatically escape special characters like single quotes with a backslash? Instead of using a C API or PHP addslashes() funciton for each field I'd need to escape? I don't think you've thought that through completely. How would MySQL know which quotes you intended to escape? If what you're asking for were possible, there'd be no need for escaping in the first place. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is the max length of IN() function?
Donny Simonton wrote:
In our case we were using words, and phrases, so we would have something
like:
IN ('a', 'apple', 'apple car', 'car', 'c') etc...
We found that once it hits about 200 or so entries the query went from 0.00
seconds to about 2-3 seconds. Sometimes much more.
I would guess that it has more to do with the amount of your key
space that the list ranges over than with the absolute number of
entries. Try comparing IN ('a', 'z') (or something similar)
with IN ('a', 'aa', 'aaa', 'aab', [...], 'aaaz') (with
lots of entries, all between 'a' and 'ab', or another small
range). MySQL can use a range of the index for the second, even
though there are lots of entries, but not for the first, which
may force it to scan the whole index.
--
Keith Ivey [EMAIL PROTECTED]
Smokefree DC
http://www.smokefreedc.org
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to store numeric data?
Roger Baklund wrote: Galen wrote: I've got a huge table going, and it's storing a load of numeric data. Basically, a percentage or single digit rank, one or two digits before the decimal and fifteen after, like this: 6.984789027653891 39.484789039053891 [snip] You should not use FLOAT, it is an approximate type, not storing the exact values you enter, but an approximation: But it's unlikely that numbers like that *are* exact values. Your advice would be applicable for prices or other situations where you're storing an exact number that has a decimal part, but these look more like measurements, so they're not exact in the first place, and any inexactness in calculations is fine as long as it's below the error in the measurements. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does dropping indexes takes such a long time?
Homam S.A. wrote: I have a non-primary-key index on a large MyISAM table, and dropping the index takes a long time, in addition to maxing out the CPU utilization in its final 1/3 interval. Why is that? You asked the question last week, and several people answered. Do you think the answer has changed since then? -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why MySQL is very slow in dropping indexes?
Homam S.A. wrote: This extreme slowness in dropping a simple index in MySQL defeats the whole strategy of dropping indexes on some tables before a huge insert operation. See http://dev.mysql.com/doc/mysql/en/alter-table.html , especially these bits: Note that if you use any other option to ALTER TABLE than RENAME, MySQL always creates a temporary table, even if the data wouldn't strictly need to be copied (such as when you change the name of a column). We plan to fix this in the future, but because ALTER TABLE is not a statement that is normally used frequently, this isn't high on our TODO list. As of MySQL 4.0, this feature can be activated explicitly. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. You want to DISABLE, not DROP, the keys. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Checking max_allowed_packet from PHP during runtime?
Martin Olsson wrote: I tried this before: ?php connectToDatabase(); $result = mysql_query(SHOW VARIABLES); $row = mysql_fetch_assoc($result); echo VALUE= . $row['max_allowed_packet']; ? But this does not work, as SHOW VARIABLES seems to be console only; not a valid query. Try looking at the result of SHOW VARIABLES. It's not just one row. It's one row for each variable, with the column names Variable_name and Value. If you're just interested in max_allowed_packet, you can eliminate the part of the result set you won't be using by changing the query to SHOW VARIABLES LIKE 'max_allowed_packet'; and then looking at $row['Value'] for the one row that's returned. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What changed in 4.1.7 to break DBD::Mysql
William R. Mussatto wrote: I've been googling for 1/2 hr w/o any answers. sorry if I've missed the obvious. Problem. Fresh install of mysql 4.7.1, AS perl 5.8 DBI and DBD-Mysql via ppm. Client does not support authnticaiton protocol What version of MySQL were you using previously? If it was 4.1.0 or earlier, then this might be useful reading: http://dev.mysql.com/doc/mysql/en/Password_hashing.html -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NULL values from LOAD DATA infile
Rachael LaPorte Taylor wrote: I'm trying to import a file using LOAD DATA INFILE into a table containing columns that default to NULL. See http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html : | Handling of NULL values varies according to the FIELDS and LINES options in use: | | * For the default FIELDS and LINES values, NULL is written as a field value of \N for output, and a field |value of \N is read as NULL for input (assuming that the ESCAPED BY character is `\'). | * If FIELDS ENCLOSED BY is not empty, a field containing the literal word NULL as its value is read as |a NULL value. This differs from the word NULL enclosed within FIELDS ENCLOSED BY characters, |which is read as the string 'NULL'. | * If FIELDS ESCAPED BY is empty, NULL is written as the word NULL. | * With fixed-row format (which happens when FIELDS TERMINATED BY and FIELDS ENCLOSED BY |are both empty), NULL is written as an empty string. Note that this causes both NULL values and |empty strings in the table to be indistinguishable when written to the file because they are both |written as empty strings. If you need to be able to tell the two apart when reading the file back in, you |should not use fixed-row format. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bug or feature, 'blah' does NOT work with null records
matt_lists wrote: I cant tell if this is a bug or a feature. Select from table where col 'blah' I use this all the time with other databases, works great, gives me everything that's not blah In SQL (not just MySQL), any comparisons involving NULL return NULL, so if that was working in some other database, it's a bug in that database. See these pages about MS SQL Server and PostgreSQL (which does have a workaround), for example: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_02_8pwy.asp http://www.sql.org/sql-database/postgresql/manual/functions-comparison.html -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
Gary Richardson wrote: There are a bunch of queries that happen on the master for statistical purposes that don't use temp tables and generate large amounts of data. These queries don't need to run on the slaves and in fact slow it down quite a bit. If the queries modify tables that are being replicated, then how would the slave remain in sync with the master if it didn't replicate them? -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ignore a single query in replication
Gary Richardson wrote: These are essentially temporary tables that aren't defined as such -- they typically take a long time to derive (30 minutes to an hour) and are used for multiple queries afterwards before being dropped. In that case, why not just ignore those tables for replication? I realize that you excluded that as a possible solution in your initial message, but that would be the normal way to do it. Without knowing why that doesn't work for you it's hard to give an answer that might. Do you not have control over the server configuration? -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
Laercio Xisto Braga Cavalcanti wrote: When you use the replace command if the row does not exist it is inserted. MySQL Reference Manual: Section 14.1.6 REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record for a PRIMARY KEY or a UNIQUE index, the old record is deleted before the new record is inserted Read what you quoted. The old record is *deleted* if it exists, and then a new record is inserted. So he wouldn't be able to get the incremented count. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (if !update then insert) sequence - result Duplicate key :(
Aleksandr V. Dyomin wrote:
$key='somekeyvalue';
dbquery(update sometable set count=count+1 where keyfield='$key');
if(mysql_affected_rows()1)
dbquery('insert into sometable set keyfield='$key', count=1');
Another possibility would be
INSERT IGNORE INTO sometable SET keyfield = '$key', count = 0;
UPDATE sometable SET count = count + 1 WHERE keyfield = '$key';
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sanity Check : Error in sql
Stuart Felenstein wrote: SQL: Select * from Education_table (and no the table is not named table) From: TypeID = Value , Type = Label Choose High School Error Msg: Unknown column 'HS' in 'field list' You need to show the exact SQL statement that you're sending. Obviously SELECT * FROM Education_table isn't the whole thing, and it's unclear what all that From: and Choose garbage afterward is. Judging by the error message, your SQL statement is trying to select a column called HS that doesn't exist in the table, but you're not showing us the SQL statement that gives the error. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modulo Arithmetic... for negative numbers
Richard Dyce wrote: mod(3-weekday(curdate()),7); But MySQL doesn't seems happy to give back negative numbers: What about changing it to MOD( 10 - WEEKDAY( CURDATE(), 7 ) ) to avoid the negative numbers? -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SUM in a special way
Mauricio Pellegrini wrote: and would like to obtain this result from a query Col1Col2Col3 1 20 20 1 10 30 1 20 50 2 10 10 25 15 3 10 10 Column Col3 should carry forward and sum values from Col2 Something like this should work, using two variables, @total and @prev: SELECT Col1, Col2, @total := IF(@prev = Col1, @total + Col2, Col2 + (@prev := Col1) - Col1) FROM table_name ORDER BY Col1; The way I'm setting @prev every time Col1 changes is a bit klugy (having to add it in and then subtract Col1 to fix it), but it seems to work. Hmm, if you change the order of the result columns you can avoid the kluge: SELECT Col2, @total := IF(@prev = Col1, @total + Col2, Col2), @prev := Col1 FROM table_name ORDER BY Col1; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Escaped BLOB data in XML
Karam Chand wrote: i have a table with a LONGBLOB column. We store some small images in it. I want to export them in XML format with schema like: cdata/c cdata/c ... ... Now the problem is even if I mysql_real_escape() and changing entities like , to lt; gt; the data some of the characters are of ascii value 12,13 etc. None of the XML parsers are able to recognise it and they throw up error? I googled but couldnt find a refernce on how to handle such characters in XML. This doesn't have anything to do with MySQL. XML isn't really designed for directly containing binary data, so people generally use Base64 encoding (or occasionally some other method of encoding binary data in ASCII). The XML parser isn't going to be able to return the raw binary data -- you'll have to decode it. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile question
sean c peters wrote: But when I load a parent table, an auto_increment column autogenerates a value that will be a foreign key in a child table. So i cant create the file to load into the child table until after the parent table has been loaded. Then i'll need to get back all the auto increment values just created, and put them into the load file for the child tables. If no one else is going to be adding rows to the tables while you're doing the loading, then you can make your own values for the auto_increment column and include them in the text file rather than letting MySQL generate them. Just find the max current value and start counting from there, and using the same values in the child tables. I do something similar for one of my databases, and it works because there's no other process for inserting rows into those tables. That may not apply to your situation, though. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enum or Int
Michael Dykman wrote: I hope I'm not opening an old can of worms here, but there are some design trade-offs in this decision. ENUM has the strong advantage of being able to constrain the contents to the specific expected values. It is not possible for an application insert an illegal value whereas using INT one would have to explicitly add a contraint to accomplish the same thing . You have essentially the same problem with ENUM: | If you insert an invalid value into an |ENUM| (that is, a string not present in the | list of allowed values), the empty string is inserted instead as a special error | value. This string can be distinguished from a ``normal'' empty string by the | fact that this string has the numerical value 0. See http://dev.mysql.com/doc/mysql/en/ENUM.html -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT if record NOT EXISTS
Adaikalavan Ramasamy wrote:
This naive syntax does not work :
IF EXISTS (SELECT myID FROM tb WHERE firstname='Jack' AND
lastname='Doe') ELSE (INSERT INTO tb(firstname, lastname) VALUES
('Jack', 'Doe');
Assuming you have the unique index on (firstname, lastname), just do
INSERT IGNORE INTO tb (first_name, lastname) VALUES ('Jack', 'Doe');
But how are you planning to handle multiple people named Jack Doe?
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select statement inbetween unix timestamp ranges
Craig Hibbert wrote: SELECT FROM_UNIXTIME(time) FROM srvlog WHERE FROM_UNIXTIME(time = '1080948600') AND FROM_UNIXTIME(time = '1080997876'); Why do you have FROM_UNIXTIME() in the WHERE clause? You said the time column was already in Unix time, and regardless, you're passing the function the result of a logical operator (which evaluates to 0 or 1) rather than a timestamp anyway. It appears that what you want is SELECT FROM_UNIXTIME(time) FROM srvlog WHERE time = 1080948600 AND time = 1080997876; or (a shorter alternative) SELECT FROM_UNIXTIME(time) FROM srvlog WHERE time BETWEEN 1080948600 AND 1080997876; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Between Operator
Craig Hoffman wrote: This should pull up all the rock climbs that are in Yosemite, that are traditional style and are between the rating 5.5 to 5.10c. Here is my query: SELECT * FROM routes, users WHERE area='$area' AND style='$style' BETWEEN rating='[$rating1]' AND rating='[$rating2]' GROUP BY route ORDER BY rating ASC ; Not sure what those square brackets are doing there, but your main problem is that MySQL has no way of knowing what order you think those rating strings should be in. The string '5.5' is greater than '5.10c', and the number 5.5 is greater than 5.10. I think you're going to need to change the way you represent the ratings -- maybe something like '5.05' and '5.10c' would work (depending on what other possibilities are, and how the letters are supposed to affect sorting). Then manipulate the strings to produce what you're used to when it comes time to display them (that, or have two columns: one for display and one for sorting). -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing selects based on date functions.
chastang wrote: select * from my_table where hour(dt)= 0 or select * from my_table where month(dt) = 6 What index should I create to optimize selects on these sorts of queries? An index isn't going to help you there unless you create separate columns for hour and month. The columns will be redundant data, but they should speed up queries. The index on the DATETIME column is useless because those queries aren't looking for a contiguous range of times; they're looking for times fitting in lots of little ranges scattered throughout the possible range of times. Now, if you were only looking for times with hour 0 on one particular day, the index should help, if you have a query like this: SELECT * FROM my_table WHERE dt BETWEEN '2004-06-24 00:00:00' AND '2004-06-24 00:59:59'; Similarly, looking for records for a particular month in a particular year should work: SELECT * FROM my_table WHERE dt BETWEEN '2004-06-01 00:00:00' AND '2004-06-31 23:59:59'; But that's apparently not what you want. Your needs are a little unusual, so they will require a table structure that's a little unusual. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Some BLOB help please.
[EMAIL PROTECTED] wrote: You might save some space if you compress() before storing. Depending on file content I'm seeing 0-50% savings? Good idea, but note that COMPRESS() and UNCOMPRESS() weren't introduced until MySQL 4.1.1. With earlier versions you may be able to compress and uncompress in your application before inserting and after selecting (using PHP's gzcompress() and gzuncompress(), for example). -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEX DESC
Alejandro Heyworth wrote: Currently, if I insert integer values into a table with an index ASC (or DESC) on the INT column , a general SELECT will return the values in ascending order. 1 2 3 4 5 That may be true, but only because you haven't been adding and deleting records. It's not something you can depend on, and it has nothing to with the index. If you want a specific order (ascending or descending), you have to specify it in an ORDER BY clause. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show table status problem
Jean Zhong wrote: mysql show table status from jean1; It gave me the following error: ERROR 12: Can't read dir of './jean1/' (Errcode: 2) In SHOW TABLE STATUS, the thing after the FROM is a database name. See here: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html You want SHOW TABLE STATUS FROM jeandatabase; or maybe SHOW TABLE STATUS LIKE 'jean'; -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Very Strange data corruption
David Griffiths wrote: But the reason modern databases have foreign keys, primary keys, not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, etc) is to prevent bad data from going in.. If no exception is thrown because you are trying to put a BIGINT into an INT, then why throw one if you try to insert a NULL into a NOT-NULL column (assuming no DEFAULT is present)? Or what about foreign keys? Why not just quietly fail if a fk-constraint is violated? But a column type isn't a constraint. If integer types were about defining a range of acceptable data, then you'd be able to define one that held only integers between 1 and 100, for example. Column types are about storage, and sorting, and maybe some other things, but they're not a good way to handle constraints, even in databases that throw exceptions. Your general point is a reasonable way of looking at things, but it's not the MySQL philosophy, and that's one of the things that makes MySQL fast. -- Keith Ivey [EMAIL PROTECTED] Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
