Slow Insert To Tables
I am inserting 70,000 Records to one mysql table, this table is a simple table with no indexes or primary keys (it consists of 3 columns), add those records takes me like 35 mins on a P4 Machine with 2.5GB of RAM. It is very slow comparing to Access or SQL Server.
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Insert On My PC But Fast On My Laptop?
I'm just wondering what can be the cause of slow inserts on my PC but super fast on my Laptop? I'm using MySql x64 on both PC. Operating System should be also identical as I mirrored it between these two PCs but PC would have more programs running.. Settings on my MySql should be identical unless there's more than just Server Instance Config Wizard. My PC is definately faster in terms of CPU and Hard drive compared to my Laptop. But insertion on laptop was like 10x faster.
View Replies !
Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different : mysql> describe MONITORINGUNIT1_DATA; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | dt | datetime | | | 0000-00-00 00:00:00 | | | wg | float(20,3) | YES | | NULL | | | dflag_wg | tinyint(4) | YES | | NULL | | +-------------+-------------+------+-----+---------------------+-------+ mysql> select count(dt) from MONITORINGUNIT1_DATA; +-----------+ | count(dt) | +-----------+ | 24144 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dt) from MONITORINGUNIT2_DATA; +-----------+ | count(dt) | +-----------+ | 1464 | +-----------+ 1 row in set (0.00 sec) Very briefly, [dt] contains an hourly date/time stamp representing when the reading [wg] was taken. [dflag_wg] contains a integer that describes the data (over threshold, under threshold, etc). The DB is populated automatically by a Python script that executes once per hour. If I want to get the overlapping data (with the same date/time stamp) I use this query: Code:
View Replies !
Slow Join On Large Tables
I have two tables: D (500,000 recs), and DL (2,500,000 recs) D has a PK and an index on HLQ. DL has a PK and an index on ID. The following SQL: SELECT HLQ as "HLQ", count(*) FROM D, DL WHERE D.DLID=DL.ID GROUP BY HLQ produces the following explain: tabletypepossible_keyskeykey_lenrefrowsExtra DALL500000Using where; Using temporary; Using filesort DLeq_refIDID4D.DLID1Using index The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00). key_buffer=512M table_cache=256 sort_buffer=16M read_buffer_size=16M It appeasrs that the 'Using filesort' on table D is due to the Group By clause and is the problem. I have an index on HLQ. Is there any way to get MySQL to use it?
View Replies !
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team. The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem. The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit. Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?
View Replies !
Slow Performance :: Navicat To Import Tables From An ODBC Connection
have been using Navicat to import tables from an ODBC connection I have. The import goes extremely fast but once the data is locally stored and I try to manipulate it by joining tables, the program goes slower and sometimes even crashes it. If the same data is however imported into a table in MS Access, and a query is created it runs fine. I would like to know if there is a way to improve the performance of my MySQL database as the reason I was using it is because of its scalability. I was wondering if there was something I was doing wrong or could do better. The Server the data is downloaded to right now is has the following specs: Windows XP Pro Intel Core 2 CPU 2.13 Ghz 2 GB RAM
View Replies !
2 Tables INSERT
I have two tables, and both have an id field. the id field in the first table is auto_increment, so when i insert data, i leave this field out. the problem is, i need to insert the same id number into my other table, but i cannot use auto_increment in the id field in the second table, because there are more than one occurence of each number.
View Replies !
INSERT Into Two Tables
How do I insert data into two tables at the same time? My DB structure is as follows: CREATE TABLE `speakers`( `speaker_id` INT(9) NOT NULL auto_increment, `speaker` VARCHAR(30) NOT NULL, PRIMARY KEY (speaker_id); ) CREATE TABLE `RSS_info`( `cast_id` INT(9) NOT NULL auto_increment, `speaker_id` INT(9) NOT NULL, `title` VARCHAR(50) NOT NULL, `filename` VARCHAR(20) NOT NULL, `description` VARCHAR(200) NOT NULL, `pubDate` VARCHAR(32) NOT NULL, `size` INT(20) NOT NULL, PRIMARY KEY (cast_id), FOREIGN KEY (speaker_id) REFERENCES `speakers` (speaker_id) on delete cascade ); TYPE=MYISAM My INSERT statement for one table is below. INSERT INTO `speakers` VALUES (NULL, 'Speaker Name'); What I need to be able to do is insert the rest of the data that I'll be receiving into the second table.
View Replies !
Insert Tables
I am using mysql through the contol panel on windows server. basically trying to build a dynamic website. In the control panel, i can see the menus to create DSN and database. Can anyone tell me where do i create the tables there is no site builder available, so should i create externally and then upload through ftp.
View Replies !
Insert In 2 Tables
I have two tables message, contacts, sch_contacts columns for table message: messageid,description columns for table contacts: contactid,email columns for table sch_contacts: messageid,contactid Now, i have to insert one row from table message and all the rows from table contacts in to table sch_contacts. insert into sch_contacts(1,(select contactid from contacts)) for ease 1 can be treated as messageid which user is going to input. I am getting error on sql which says subquery return more than 1 row how to insert all the rows from 1 table into another?
View Replies !
2 Tables And An INSERT
im trying to copy a row from one table to the other, but 1 of the table's has an extra AUTO_INCREMENT column. INSERT INTO fixtures_h values (NULL, SELECT * FROM fixtures WHERE No = 1).
View Replies !
Insert Data Into 2 Tables Using Php
i was wondering how i would go about insert data collected on a php form into 2 separate tables. i want to collect email and password for an admin table and everything else for a separate table why cant i just do this? $query = "INSERT INTO user_admin (s_email, s_password, salt) VALUES ('$email', '$encrypted', '$salt')"; mysql_query ($query) or die ('Could not register new account.'); $query = "INSERT INTO student (s_forname, s_surname, s_uni_id, s_email, s_course,) VALUES ('$forename', '$surname', '$uni_id', '$email', '$course')"; mysql_query ($query) or die ('Could not register new account.2'); the email and password get stored into the user_admin table but nothing is collected for teh student table do the values such as $forename need to be declared anywhere else before?
View Replies !
Insert From Multiple Tables
is it possible to write a query that will allow an insert, that selects information from multiple (but not joined) tables, e.g. something like Insert into table1 (col1, col2, col3) SELECT a.col11, b.col21, c.col31 FROM table2 a AND table3 b note that table2 and table3 have no common information so I can't use a simple Join statement.
View Replies !
How To Insert Data Into 2 Tables At Once
I am realizing now that I have never done this before. I need to insert information - all at once into two seperate tables joined by a fk. So essentially I need to insert into one table then get the primary id from it and then insert this value long with the other fields - into the 2nd table. Can I do this without writing 2 insert statements - perhaps using a join? If so what is the basic syntax?
View Replies !
INSERT Values In Three Tables Using ID Column
how to insert values in different tables? Table t1 got a column named "id" and this value should appear in t2.t1_id In other words: How to fill all columns in all tables with one INSERT / UPDATE ? t1 id bigint autoincrement name varchar() bla bla t1 id ....
View Replies !
Insert Data Into Related Tables
I'm sure that this question has been asked many a times but I can't find it, probably don't know the right keywords to find it. I am new to MySQL, working knowledge of MS Access, so quite a leap forward. I have 2 tables (for learning purposes) 1. pages page_id page_title ... 2. articles article_id page_id article_title ... If a create a new article_id for which a page_id does not already exist, what method must I use. Basically how can I create a new article and page entry simultaneuosly? Must I do 2 seperate inserts (is this when a transaction should be created) or one using some sort of join? How do I ensure that whatever auto-increment page_id number is created by the database is associated to my new article entry?
View Replies !
How Do I Insert An Auto Increment ID Into Other Tables
I have been trying to insert An auto_increment ID of one table into other tables and I am getting wierd results. I am trying to insert the CLIENT table primary key into the other three tables and it is inserted correctly into the CONTACTS table but the JOBSHEET and KNOWLEDGE tables both insert 1 into the clientID field. Code:
View Replies !
Any Way To Insert Data Into Two Tables At The Same Time?
what I have is a table called `order` and a table called `lmcharge`. a person inputs data from a form and i need to take the data from both and some goes into one table, some goes into another here is an example: order values: student_id, class_id, order_type, date, status //student_id, class_id, order_type will be from the form lmcharge values: student_id, order_id, lm_number //student_id, and lm_number will also be from the form but order_id will have to be from the order table anyone have a clue?
View Replies !
Insert Data Into 10 Multiple Tables
Can I insert data into multiple tables at once? Basically just need to insert a number into the 'membersID' column into 10 tables, but is taking ages, so wondering if I can write a query which targets all the tables or is this impossible in SQL?
View Replies !
Insert Into Single / Select From Multiple Tables
I have two tables from two databases (joomla2.jos_content and maxdev.jos_content) that I need to pull data from in order to populate a single table (joomla2.jos_magazine) Here is what I want to do (I know this query doesn't work but you get the idea what I am trying to do) insert joomla2.jos_magazine_articles (joomla2.jos_magazine_articles.name, joomla2.jos_magazine_articles.article_id, joomla2.jos_magazine_articles.category_id, joomla2.jos_magazine_articles.catid) select joomla2.jos_content.title, joomla2.jos_content.id, joomla2.jos_content.catid, maxdev.jos_content.catid
View Replies !
Insert Data Into 2 Tables With 1 Query Fails
I found 2 old threads on how to insert data into 2 tables with 1 query. Both of them said that i should seperate the inserts with a ;. But i must be doing something wrong, because it comes up with an error like this ".....right syntax to use near ' INSERT INTO eiland_details" What am i doing wrong? It's possible, right? To insert data into multiple tables with 1 query? $query = " INSERT INTO sub_pages (mainpage_id, intro_text) VALUES ('$main_data->mainpage_id','$sub_intro'); INSERT INTO eiland_details (inwoners, oppervlakte) VALUES ('$detail_inwoners',$detail_oppervlakte')";
View Replies !
Lock Tables To Update And Delete, But Insert New Records
I'm trying to "lock" mysql tables - to prevent rows being updated/deleted - I still want to be able to insert new rows (legal reasons). I have toyed with the idea of changing the user permissions - can I simply change it so that no-one can perform an update on that table, even root? But I feel there must be a simpler way that I and my many web searches am missing.
View Replies !
INSERT Syntax For Text File To Load Into Tables?
I have successfully created a number of tables in my database and am looking for an efficient way to load data into the tables. I have the following (successful) command to find the text file and load it into the target table: "load data local infile 'C:mySQL abledata.txt' into TABLE checks;" It loads into the 'checks' table successfully, but the data is incorrect. What is the syntax of the INSERT command that I'm using in the text file itself-- assuming that I do use the INSERT command? Here is what I have in the NotePad text file I'm using, which only produces zeroes in the first and third columns: insert into checks values ('1', 'Ma Bell', '150', 'Have sons next time', '2', 'Reading R.R.', '245.34', 'Train to Chicago', '3', 'Ma Bell', '200.32', 'Cellular phone', '4', 'Local Utilities', '98', 'Gas', '5', 'Joes Stale $ Dent', '150', 'Grocieries', '6', 'Cash', '25', 'Wild Night Out', '7', 'Joans Gas', '25.1', 'Gas');
View Replies !
Insert Multiple Rows With One Insert Stmt And Nested Select
I'm trying to insert several rows into a table using only one insert statement: insert into component_feature values (select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_id = 1) When I run the select statement alone, I get the result I want: +---+---+---------------------+---+ | 3 | 1 | software_feature_id | 1 | +---+---+---------------------+---+ | 3 | 1 | 0 | 1 | | 3 | 1 | 1 | 1 | | 3 | 1 | 2 | 1 | +---+---+---------------------+---+ But when I run the complete statement I get: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select 3,1,sf.software_feature_id,1 from software_feature sf where sf.software_i' at line 1 This query is part of a PHP application I'm building. I'd rather not have to write a PHP loop to do multiple inserts.
View Replies !
Mysqldump Options :: --extended-insert --complete-insert
Adding options like extended inserts or complete inserts (or both) make no difference to the output file. Why? mysqldump -uroot -pmypass! --databases test --tables proxy_view > mytest.txt mysqldump -uroot -pmypass! --extended-insert --complete-insert --databases test --tables proxy_view > mytest.txt Both the commands mentioned above saves a same set of commands. insert into tablename (column names) values (set of values), (another set of values), (one more set) Whereas I want insert into tablename (column names) values (first set); insert into tablename (column names) values (second set); Ok. --skip-extended-insert is what I was looking for
View Replies !
How To Insert Multiple Rows With 1 Insert Query
I am having a form on the front end which has for example 3 rows each with 3 columns. The user enters data in all the 3 rows. When he hits the add button these should get in the database. What insert query would I write to add all of them together to the database? Do I need to use some procedure?
View Replies !
Slow
What generally would be the reason why all my db driven sites are running slowly or even hanging. I am on braodband speed but just changed hosts.
View Replies !
Update Too Slow
I need to update 25 * 5000 records, if I do one at the time it takes too long time, do any one have a good proposal ?
View Replies !
Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:
View Replies !
MySQLdb Slow
I have a Python program that parses a file and inserts records into a database with MySQLdb. I recently upgraded to MySQL 5.0.8, and now my parser runs *really* slow. Writing out to CSV files is fine, but when I try to insert the same records in a MySQL5 database, it slows to a crawl. Using MySQL 4.1 seems fine. The data seems to be inserted correctly, it's just really slow.
View Replies !
Slow Restore
Mysql 4.1.15 on Win2k. Using InnoDB. Using the mysql administrator gui to create a backup, everything goes fine, and restores quickly. Using the command line: mysqldump %dbname% --single-transaction > %dbname%.sql creates a file about 15% smaller than the gui produces, and is EXTREMELY slow to restore. I have tried adding locks, skip opt, everything. What does the gui use for a command to create this dump?
View Replies !
HEAP So Slow
I have a heap database, with 1.5-1.6 milion rows. on that is 2 columns... ID | Title title is indexed. When i run a query like this ------------------ SELECT index_data.* FROM index_data INNER JOIN `index` ON index_data.id=index.id AND index.title LIKE '%$query%' WHERE playtime > $dur... The execution time is about seconds... ------------------ Even a single like statement just on `index` (heap) takes 3-6 seconds. Here's the table stats... ---------------------------- Data 397,442 KB Index 24,639 KB Total 422,081 KB ---------------------------- Why??
View Replies !
Too Slow Queries
I am using version 5.0.4. I noticed that suddenly my queries were becoming too slow. I have data in three tables, with 40,000 rows, 50,000 rows and 70,000 rows respectively. I was able to run queries that joined the three tables together and get the results in less than 5 seconds. I tried updating the tables through a program that used a stored procedure to delete the tables but I had some other problems. Since the I restored the data in the tables from backup databases. Now I can't run any queries that join the three tables together, it waits for a long time and then times out. The data in the tables look good. Now I can't get the results from any of the other backup databases either. It look like the entire MySQL server has slowed down greatly. I tried rebooting the server to no avail.
View Replies !
Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that. MySQL said: Documentation #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)
View Replies !
Slow Db Access
I have worked with a few mysql dbs on different servers but i have recently been asked to work with one on nicnames. It seems horribly slow. Working in phpMyAdmin (which i had to install myself) it takes ages when i want to do anything. View the table structure, view the data..etc. Any way i can test the speed so that i can compare it against another server i work with and proove there is a speed issue and take it to nicnames cus it is crazy and is going to affect the speed of the website!!
View Replies !
Slow Insertions
I have a MyISAM table with fixed width rows (ie no vars). I also have keys disabled. I am running a huge data load process. When it first started it was inserting about 38 records a second. Now, after about 200,000 records, it speed is about 22 recoerds per second. Whats going on here? and how can I improve it? I am loading about 15 million records, so the difference between 22 per sec and 38 per sec is SIGNIFICANT (ie 2 days).
View Replies !
Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table. Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.
View Replies !
Slow Queries!
I have a website which has a users table in a mySQL database. This users table is large (It has about 25 columns - most varchar(100)) but only has about 10000 records. The records contain user information which is searched with a javascript form. My problem is that when I click to 'view all', it takes about 7 seconds to load. This seems a lot? Does 25 cloums seem sxcessive in a table? Can anyone point me to some good tutorials / docs on improving query performance? I have defined the colums as best as I can, but I am using SELECT * from table, would selecting individual columns make a big difference?
View Replies !
Slow Subquery
Can anyone tell me why the following query with sub-query takes forever to finish? (I've le it run for 20 minutes, and it still hasn't finished) select date from temps where date in (select distinct date from observations where camera like "a") The sub query returns 10 dates. The outer query is on a table that contains about 40,000 rows. What's the big deal here? All I'm trying to do is select rows from "temps" that match a small range of 10 dates. Is there another way to do this? Is a sub-query the wrong approach?
View Replies !
Slow Connection
I build an application and installed it on many machines. In every machine except two, the program works without problems. On this two, the connection with database is too slow. I saw the opened doors with 'netstat' and the computer opens about 5 or 6 ports (to the port 3306 of the mysql server) before sucessfuly connect with MySql Database and execute the sql. I don't know what could be happening. I realy need to fix this because the progrm is too slow with this error. Could anyone know what could be happening??
View Replies !
Slow Query Log
my slow log is catching a slow query, however the timestamp for the query is "0". I also placed a timestamp on the query to echo out to the results page, and it is about 4 thousands of a second. Why is it showing in the slow log?
View Replies !
Slow Query Using NOT IN
I am migrating a MSSQL server to MySQL. I know the following SQL is valid for both servers, but MSSQL finishes execution of the query almost instantly, and MySQL has been running the query for the past ten minutes and still is not finished. There is basically the same amount of data in each database. Does anyone know ....
View Replies !
Slow Subqueries
I know (by internet) that mySQL is very fast. Problem is that my subqueries that are very fast in Access or SQL Server but they are very slowly in mySQL - since I have to restart my computer because mySQL freeze all the processes. (the resources where used at maximum - 2 Gb RAM, 2,5 Ghz processor) This is the query : SELECT NPL, PP FROM P_A INNER JOIN ACTIONS ON P_A.NA =ACTIONS.ACT_N WHERE P_A.NA in (SELECT NA FROM P_A WHERE NP ='ABC') P_A has 5 columns and 12000 rows Actions has 5 columns and 770 rows Any suggestions ?
View Replies !
Slow Request
I want to run the following request: mysql> select count(*) from fingerprints where fingerprint in (select fingerprint from fingerprints where id_file=3263); where fingerprints is the following table: ....
View Replies !
|
TRACKER
