SELECT Takes 20 Seconds
I am having trouble speeding up a SELECT statement from a table with 1,764 records.
I tried myisamchk --sort-index --sort-records=1 but this did not help (is a Primary key enough?).
Things were fine at around 1200 records but went downhill after 1700. I am using MySQL 3.23.42 on HP/UX 11.11 and PHP 4.3.4. Any tips on how to speed things up?
View Complete Forum Thread with Replies
Related Forum Messages:
Seconds Between 2 Dates - Why Does Seconds Jump By 40 Seconds?
I have a table for my sessions and basically i wanna check seconds between NOW() and when it was created. I noticed that at every minute the seconds would jump by at least 40 seconds for every minute. So if i created a new sesson on my site and Log NOW() into created then i try doin SELECT (NOW() - created) AS created_sec I was monitoring it because my site would log me out all the time. So I noticed at every minute it seems that it always jump 40seconds or more for every minute. Here is table:...
View Replies !
Easy SELECT With OR Takes A Very Long Time
We've got a database with about 1000000 books. A query in the table BOOKS for the TITLE 'java' is very fast. We have a fulltext index on the column TITLE. However, if we want to do a exact same query and include the rule that the book with ISBN '0131016210' always should be included - then the query take several seconds to finish: SELECT * FROM C_BOOK WHERE MATCH(NAME) AGAINST ('java') AND (ISBN LIKE '0%' OR ISBN LIKE '1%') OR ISBN = '0131016210' ISBN is the PRIMARY KEY. Is there anything I can do about this?
View Replies !
3 Seconds To Run A Select That Returns Empty Set?
I have recently created a very simple table with 2 VARCHAR fields. I have then created a quick php script to loop through some inserts (inserted 600000 rows) and then deleted all the rows by doing "delete from Table where field1="abc" which should delete all rows. I then did a select * from Table and the query took 3 seconds! The return result set is empty! Later I found out that if I do a delete * from Table, without a where clause. And then after tat do a select * from Table, it will take 0.0 seconds. Why is this? How to fix?
View Replies !
ORDER BY Takes Forever
I'm having problems with ORDER BY. When I run a select targeting just one of my tables, it runs quickly and nicely. When doing the following: select * from rubbet, kommun where ortnamn like '%Johannes%' and rubbet.harad=kommun.harad and rubbet.socken=kommun.socken order by rubbet.ortnamn; it screws up bad. The query takes about 2 minutes to run before showing the result: "74 rows fetched in 0.0032s (112.5129s)" obviously the query takes no longer than 0.0032s but the ORDER BY clause makes it take forever to bring back the result. without the order by the whole thing runs very smoothly.
View Replies !
Update Takes Too Long, I Need Help
here is my update SQL string: update prices, legend set prices.legend_id=legend.id where ( (prices.id between 1 and 10006) ) and prices.a_legend=legend.description running time: 63.97960 as you see prices has 10006 records and legend table arround 349 records please advice how to speed up this?
View Replies !
Subquery Takes Forever
I have two problems where I'm trying to retrieve data using a query. Part 1: I am doing a simple sub query which is on a two data sets with no more than 3000 rows in total. The query is taking 28 seconds to execute? Why I don't have a clue? Quote: 'Select Title FROM products WHERE Product_ID IN (SELECT Product_ID FROM order_items WHERE Order_ID="'.$id.'")' Part 2: When I have solved the above I'm looking to do a join, but don't know how to approach it ? Basically I'm trying to get a complete data set for a product/order like the following: SELECT * (which includes product_id) from items AND SELECT Title from products where product_id =(the product_id is retrieved from items); // using a join
View Replies !
Create Index Takes More Than 5 Hours
I'm running MYSQL on a windows xp laptop with an intel centrino 1.60 GHz Processor and 512MB RAM. I've created a table and imported 27 million rows of data. I wanted to create an index on one char(55) field, as this field is a key against which many selects will be made. The information in the field is not conducive to having an integer key as any queries would require an extra join to the look-up (reference) table. Creating the index ran for more than 5 hours and finally gave up when there was no more space on the drive (creating the index apparently consumed more space (11GB) than the actual table (8GB). I'm wondering if this is normal time and space consumption for 27 million rows. Finally, doing an un-indexed query on this table : SELECT * from historical where reference = 'abcd'; Takes more than 1/2 hour. Is this also a normal amount of time? I understand that people are using MYSQL for data warehousing and major transactional applications.
View Replies !
Query Takes A Long Time
On my site I have a query that searches through 1,7 million. The php-file with the query takes a bit of time to load. Is it possible to show some sort of progress-bar during this time?
View Replies !
Results In Multiple Pages, Takes Too Much Time
I have a table of a million records and wrote a CGI-PERL script to display the results based on the user input. The results might be anywhere from 100 to 1000 per query and presently I am displaying them as 25 results per page. Problem: Each query is taking about 20-30 seconds. My solution: I have tried to optimize the table and also index the table. I have actually converted a MS access database to SQL database, so it wasn't previously indexed. Both optimization and indexing doesn't give any good results. I always get a timeout. ie. it takes longer after indexing and optimizing. 1. I was wondering if someone has a creative solution for this. ie. reduce the time from 20-30 seconds to atleast 10 seconds. 2. I have links of pages of results beneath the first page result. When each of these links are clicked it takes 20-30 seconds again. Is there a way I can reduce the time taken for the subsequent pages are reduced? I cannot use the LIMIT option in mysql, since I have a where clause which has to search through the whole table. I tried using views and using limits, but it takes as much time.
View Replies !
JDBC Result Closing Takes Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week. Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.
View Replies !
Backup With Mysqldump Takes Hours, Database Is Not That Big
Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb). My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all. My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk. The database is around 350mb, with around 1.1 million rows. To backup I use mysqldump -uroot -p database > backup.sql As I said, the above takes hours. I guess it should take minutes?
View Replies !
Delete All Records In A Table Takes Forever -- Anyone Know Why?
I have a table called table_a that has 1 record in it. I delete that record, which because of foreign keys (the tables are type InnoDB) will cause the records in 8 other tables to be deleted. The 8 other tables have a maximum of 200,000 records in them. 2 of them have that many while the remaining have < 5,000 records. Currently, deleting that 1 record has taken hours upon hours with no end in site. Does anyone know why in the world this would take so long? Is there any way to speed it up (maybe a config setting I am missing or something)?
View Replies !
How Many Seconds Query Took?
I'm sure this is one of the easiest questions to answer, but how to you know how many seconds it took to query the database? I noticed it on the command-line client and on a lot of forums.
View Replies !
Changing Value After 30 Seconds?
Is there a way to change a value after 30 seconds of it being changed? So if I change it to 1 from an external script, I want to 30 seconds later to change to 0. Is this possible without a script (or a script in sql of some sort?)?
View Replies !
JDBC Result Closing Takes Ists Time
i am reading 1000 Elements from a database (500.000 all together). The problem is reading the data takes only a few miliseconds but closing the ResultSet takes another 22 seconds. My Hard Disk tells me that the ResultSet is probably running to the end of my table row by row. Is there a way to tell mysql not to do this ? Statement tempStmnt = aConnection.createStatement(); tempStmnt = aConnection.createStatement( java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY); tempStmnt.setFetchSize(Integer.MIN_VALUE); Date tempStart = new Date(); ResultSet tempRs = tempStmnt.executeQuery("SELECT * FROM ARTIKELSTAMMDATEN"); int tempXx = 0; while (tempRs.next() && tempXx++ < aCount) { tempRs.getString("artikelBezeichnung"); }
View Replies !
Adding Seconds In TimeStamp
I have a table that contains an "interval" column and a timestamp column. I want to set the default value for the timestamp column to be the current timestamp plus the interval column which is a value in seconds.
View Replies !
Add Seconds To Current Time
I am trying to create a statment to add a given time in second to the current time like this: update tags.triggers set next_interval=(addtime(curtime(),300)) where id=1; This statment adds 3 minutes to the current time. How can I add 300 seconds to the current time?
View Replies !
Mysql Does Not Respond For Several Seconds.
I have created a new database-driven website. I've installed php 5.1.1 and mysql 5.0 I'm working with the Mysqli connector. Everything works fine. Except when a user starts tampering the website by continuously hitting the F5 button. On the intro-page of the website, a lot of queries are being fired against the database. When the user hits F5 quickly for, let's say, 50 times the database times out and I get the ugly error: "Warning: mysqli::mysqli() [function.mysqli]: (HY000/2003): Can't connect to MySQL server on 'localhost' (10048) in..." After the database 'hangs' and I am unable to connect to it for at least 30 seconds... Any idea why the tampering makes my database go down for a few secs ?
View Replies !
Is It Possible To Bypass 30 Seconds Time Out
hello im importing quite a large amount of .sql scripts into mysql and everytime i get this error Fatal error: Maximum execution time of 30 seconds exceeded in www.******* is it possible to alter this default timeout to a longer timeperiod and if so how is it done, im using MySQL v5.0.27, together with PHP v5 and phpmyadmin v2.9.03
View Replies !
MYSQL Database Alteration, Repair And Restore Takes Huge Time
I am trying to run alteration queries on one of my mysql tables which has more then 22,00,0000 lakh records. Its been 23 hours and the process is still running (I have to close all the sites running on server due to same). My server specifications: Red Hat 9.0 Pentium 4 3.0 GHz 2 GB Ram, Burst RAM 5 GB Running webserver, mail server as well. Is there a way I can view the minute process details, as what table record is being updated ?
View Replies !
Lost Connection To MySQL Server During Query After Script Takes 60 Secs
I get the error "Lost connection to MySQL server during query" at the mysql_select_db when reaching this part of the code after 60 seconds of script execuation has already passed. Works fine if the script execution time when reaching this is under 60. Tested with Sleep() and I'm 100% sure that the 60 second barrier is causing it. I just don't know what the barrier is.////
View Replies !
Mysql Killing The Connection After X Seconds
I'm running mysql 4.0.20a on Windows XP Pro. I have a java web application that connects to the database for storing persistent java classes. I am running into a problem when I leave my application running overnight... I try to log in to my page in the morning and it gives me a JDBC Exception. If I restart the web server (not the mysql server) and then try to log in to my app runs fine. In trying to troubleshoot I've used the mysql control center to show the processes of the mysql server and it shows that my web app doesn't really disconnect after running a query, it just sleeps. Does mysql kill a connection if it has been sleeping for a long time? How do I change this? How do I find out how many seconds of sleeping before mysql kills the connection?
View Replies !
Difference Between Two Dates Including Seconds
1) I have mySQL 3.23 installed. I have a record with two date/times fields in YYYY-MM-DD HH:MM:SS format. How do I find the difference between those two date/times? (I would also like to know it with seconds left off.) 2) Let's say I have two records each with a date/time field in YYYY-MM-DD HH:MM:SS format. How do I find the difference in date/times when each field is in a different record?
View Replies !
Delete Inactive Sessions Every 60 Seconds?
I have a session table, where I stored user sessions in the database in a memory table. My question is, performance wise, would it be smarter to delete inactive sessions every 60 seconds via client http requests, or would it be wiser to select active sessions via a select that uses a where clause and searches a timestamp index column for active sessions, and then a cron job is scheduled to delete every 30 minutes. Which of these would be the better choice? On each page request I am going to have to select the number of sessions that exist and a second query that displays the active users. So if I go with my second choice, I will have to include a WHERE clause that scans the index on both those queries. But if I am issuing a delete every 60 seconds, then I won't have to do that, but then the delete is taking place every 60 seconds on a client request.
View Replies !
Sql Error - Shuts Down The Website In 5 Seconds
There is a program called as D4 and it sends a huge data to the sql database and it slows down the website in the first 2-3 seconds and then in a few seconds it shuts down the website until s/he stops using the program. We made a huge research over the internet, but we couldn't find any solutions for this. It can shut down any website, that's having a sql based database. I hope you can help us with this problem. Thank you for your attention and time. if the site admins want to get the program, they can contact us. However, they should be aware that this is a dangerous program so it shoudn't be distrubuted.
View Replies !
Avoid Duplicate Records In Within 30 Seconds
I'm working with php. I have a auction site, more or less. I want to create all-time rankings. The idea is to display where a seller ranks (all time) in the number of sales. So for example, I'd display John Doe All Time Sales Ranking: #138 I'm not exactly sure how to go about this. $query = "SELECT count(*) as counter, SellerName FROM sales GROUP BY Sellername ORDER BY counter DESC"; This query would give me the data to list all of the Sellers in desc order by the number of sales. In php, I could probably count until the Sellername was equal to $Sellername (already defined in their profile page), but I was hoping there would be a way to do this entirely in MySQL.
View Replies !
Converting A TIME Field Into Seconds : Error Message
CREATE TABLE `m010` (`date` date NOT NULL, `hour` time NOT NULL, `duration` time default NULL, `cs` int(11) default NULL, `id` bigint(20) NOT NULL auto_increment, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC the field 'duration' is in TIME format (thus 00:00:00) .. I want to convert it in seconds. I've read that I can do something like this: mysql> select duration, TIME_TO_SEC (m010.duration) AS 'ts' from m010 WHERE id = 736; Problem is, I get this error : ERROR 1305 (42000): FUNCTION m010.TIME_TO_SEC does not exist
View Replies !
MySQL 4.0.18 On Dual AMD64 Opteron Causing Thread Zombie Even When Timeout Is Set To 60 Seconds
We have a problem with Dual AMD64 Opteron/MySQL 4.0.18/Mandrake 10 for a very high volume site. We are evaluating the performance on our new server AMD64 and it seems it's slow compared to Dual Xeon/MySQL 4.0.15/RedHat8 and Dual Xeon/MySQL 4.0.18/Mandrake 10. And it seems there are zombie threads. 570 threads in 1 hour and we didn't even use JDBC connection pooling at all. These threads are supposed to be gone within 60 seconds, since we set that option in mysqld. Note that we run many SELECT queries (can be up to 150 queries/seconds), but the system does not indicate any slow query: it's 0! Our configuration is Apache 2.0.48 + Tomcat 5.0.27 + MySQL 4.0.18 with MySQL connector/J 3.0.14 (latest stable). The Redhat 8 runs on Apache 2 + Tomcat 4.0 + MySQL 4.0.15. The old Redhat 8 on Xeon was fine. We have another machine running Mandrake 10 on Xeon and they were fine under the same load. I have set the wait_timeout to 60 seconds, and it appears to be fine within 10 minutes, all the threads that are in "sleep" mode disappeared after 60 seconds. After a few minutes though, it's back like it was before. Is this Mandrake problem? MySQL problem? I read in here than Mandrake win hands down on AMD64 compared to FreeBSD. Code:
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Maximum Execution Time Of 300 Seconds Exceeded (was "MYSQL Error")
I have a huge database 400mb+ in size which i have exported into a .sql file. I tried to run this export script into a new database and it runs fine until three quarters into the scripts execution and the following error appears: 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 '<br / > <b>Fatal error</b>: Maximum execution time of 300 seconds exceeded in <b' at li ne 1 I believe this error may relate to "delayed_insert_timeout = 300". How do I increase this limit in mysql - maybe it may solve the problem but then again it may not . Any advice is most gratefully appreciated - As you can imagine if the script runs fine for 3/4 of it and then an error appears, stating the error is on line 1, it will take me days to go through the whole script (hundreds of thousands of rows) to find the error as I assume the error does not reside on line 1 as this line would have been executed. I am running the script on mysql 5, and via source /path/****.sql on linux.
View Replies !
INSERT INTO SELECT Taking Forever But SELECT Part Is Fast?
I have the following mysql code to insert about a million rows. This takes quite a long time (over 50 min). However, when I just run the query alone without the INSERT command, the query itself only takes under 5 minutes. Is there a way I could speed up the inserting?? insert into tblc select tbla.* from tblb inner join tbla on tbla.issuer_id = tblb.issuer_id;
View Replies !
SELECT INNER JOIN Performance VS Single Table SELECT
I have designed my database using a somewhat oriented approach. Rows are objects, and different type of objects are in different tables, but since my objects share a common set of fields like ExpireDate, Archived, Draft, CreateDate, CreatorID, etc; I have an 'objects' table with these columns, and I have set up a foreign key in other tables where there is a need for a row-to-row integrity. Now my question is, since I have to SELECT using INNER JOIN with object and the corresponding table, merely to filter out archived rows, I am wondering if I would be better off actually putting the common fields into each respective table and get rid of the 'objects' table altogether. I mean, is it more job for the server to actually JOIN the tables for each SELECT versus having a clean design with object oriented approach ? How bad is my design, and what are the recommendations of experts who obviously were tempted to create an object oriented database design?
View Replies !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
View Replies !
Speed Diff Between Select * And Select Column
I have this question that I cannot find a right answer among my friends. Will like to know: Assuming I have a table with 8 columns. When I am querying these records with specific WHERE conditions, will SELECT * be faster, or SELECT (3-5 x columns) be faster?
View Replies !
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
|
TRACKER
