Re: How to overwrite existing file with SELECT .. INTO?
Hello Mike, AFAIK, What you have specified is applicable only for tables! But for file in "Select ..into outfile", sure Exit handler is declared when we try to overwrite an existing file! Thanks DEVI.G - Original Message - From: "mos" <[EMAIL PROTECTED]> To: Sent: Sunday, April 15, 2007 8:36 AM Subject: Re: How to overwrite existing file with SELECT .. INTO? At 08:14 AM 4/13/2007, Amer Neely wrote: I'm using MySQL 5.0.21 and am trying to find out if it is possible to overwrite an existing file when using a 'SELECT ... INTO' command from the command line. Is there another parameter that can do this? I've looked through the online reference manual, but found no specific help there. -- Amer Neely Amer, Why can't you do: drop table if exists mynewtable;select .. into mynewtable Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.446 / Virus Database: 269.4.0/762 - Release Date: 4/15/2007 4:22 PM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
state=sending data for too long
Hello,
For the following query:
select unix_timestamp(beginTime) as bTime,myTable.* from myTable Where
nodeID=2 AND flavor In('sim','ofl') AND (deactive= 0 OR
deactive>=1176682388) AND unix_timestamp(entryTime)<=1176682388 AND
beginTime<=from_unixtime(1546300816) AND elementID In
(491507,491508,491509,491510,491511,491512,491513,491514,491515,491516,491517,491518,491519,491520)
Order by beginTime desc limit 14
EXPLAIN shows
++-+---+--+---+-+-+---++-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+---+--+---+-+-+---++-+
| 1 | SIMPLE | myTable | ref | PRIMARY | PRIMARY | 4 |
const | 403782 | Using where |
++-+---+--+---+-+-+---++-+
Show processlist reveals that the query state sits in sending data for a
very very long time.
Perhaps I need a tunning parameter adjusted. Does anyone know which one,
or have any other ideas?
Thanks,
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is an index on varchar column efficient?
At 06:23 PM 4/15/2007, you wrote: Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times Indexes are the least of your worry. The code you've posted is extremely inefficient. The fastest way to load the data is using a "Load Data Infile" in which you could load the data in a few minutes. As it stands, it could take a few hours or even a couple of days. Every time the Insert statement is executed, it will lock the table, preventing others from reading from it. So the speed of accessing the table during the inserts is going to be very very slow. You'd be better off inserting the data into a temporary table and then if the data looks ok, insert it into the production table a few thousand rows at a time so it doesn't lock out people that are using Select statements. Use something like: insert into table1 select * from tmptable limit [offset,] 1 and keep bumping up the offset each time its run. If no one else is using the table1, then just load all the data at once without the limit clause. alter table test add index (phonenumber) Also, I can't modify the code that calls the SQL. It was written in VB and the developer has left the company with the source. It's a good thing that programmer left the company, because writing code like this would get him canned. If your company paid for the source, contact a lawyer and hunt him down for theft of company property. It shouldn't be too hard to figure out where he works. His behavior should also impress his new boss too. :) I would like to get your feedback before I actually do this because I don't want to create a disruption when the DB is in production. If you can't first load the data into a temporary table, then you'd better expect a lot of down time as it tries to load 2 million rows, 1 row at a time. If it were me, I'd rewrite the code from scratch. Make sure you have sufficient memory for key_buffer_size because building an index will take less time if it fits in memory. Good luck, and remember to make lots of coffee because it could take a while. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is an index on varchar column efficient?
murthy gandikota wrote: Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times alter table test add index (phonenumber) An index is probably beneficial. You can find the cardinality of the proposed index (number of distinct values the column has) with COUNT(*) queries. If you divide this and the number of rows in the table, you get the index's selectivity. In general you want a selectivity greater than 1/3 for the index to be useful. Higher is better, all other things being equal. Supposing you have 2 million records, SELECT COUNT(DISTINCT phonenumber)/200 AS selectivity FROM test; Now, you may be able to index just a prefix of the column and still get as much or almost as much selectivity. Try this: SELECT COUNT(DISTINCT LEFT(phonenumber,5))/200 AS selectivity FROM test; Play with the number of characters until you find a good balance between the selectivity and the space the index will take. The smaller the prefix, the smaller the index. In the end you will have to profile the queries you're going to run to figure out exactly what will be the best for your situation. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: can't import
Now, would someone please explain *why* this worked and all my other attempts didn't? Was it because of file permissions? or the linux user id? or that I put a passwork on the mysql root account? or that I changed the ownership of the data file? or that I took out some spaces on the data file? Or, ... ? What did *you* do differently this time? Obviously the user is different, but what about permissions? What were they before? 644 should have worked previously but since we don't know what they were before we can't tell you. It has nothing to do with the mysql password or the spaces in the file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is an index on varchar column efficient?
Hi, I have a table with 2 million records but without an index or a primary key. The column upon which I want to create an index is a varchar. Will it give me any advantage when I have to do a select on the column? BTW, here is the scenario: create table `test` (`phonenumber` varchar(100)); insert into test (phonenumber) values(`0001234567'); and so on 2 million times alter table test add index (phonenumber) Also, I can't modify the code that calls the SQL. It was written in VB and the developer has left the company with the source. I would like to get your feedback before I actually do this because I don't want to create a disruption when the DB is in production. Thanks Murthy - Ahhh...imagining that irresistible "new car" smell? Check outnew cars at Yahoo! Autos.
