MYSQL :: Query Takes A Long Time

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?

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?

Take Long Time To Run A Query!
I have a webserver that I just use for a webapplication. PHP, Apache and MySQL is installed on the server.

The size of MySQL database is 10 GB. End inn it is millions of rows.

I wonder where I search what is the normail time the data to return? In my case it takes from 20 sec to 2 min to run a query!

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.

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");
}

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");
}

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 ?

Restore :: Taking Long Time
I've got a table with over 400,000 records. I did a mysqldump to ensure I could restore it in case of problems. Sure enough, problems arose and I needed to do a restore. The mysqldump created the usual create database, create table and one gigantic insert statement, where all 400,000+ records are inserted. I started my restore around 10pm or so yesterday and was surprised to wake up this morning and find it still running. Considering the dump itself took a couple of seconds, why is the restore taking so long? I'm building a website that's going to have tons of traffic. A database restore that's going to take multiple hours seems to be unacceptable. One way to speed up the process would have been to simply copy the actual database files and just stick them back in the data directory.

Update Taking Long Time
everytime it tried to update row in any table, it takes very longtime, it locks the table, then i run out of connections and mysql crashes.

Indexing Taking Long Time
I am currently importing about a million rows into temp tables - then the temp tables are indexed and finally the original tables are dropped and the temp tables are renamed - The indexing of the temp tables takes a considerable amount of time - My question is - Would there be a difference if i index the temp table before importing as opposed to after??

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

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.////

Long Query
Heres my query that will not show up the correct data

SELECT * FROM `table` WHERE (`field1`="data1" AND `field2`="data2") AND (`field3` LIKE '%abc%' OR `field4` LIKE '%abc%')

It seems not to factor in the first two field's correctly.

When Is A Query Too Long?
I'm just curious to other opinions on the whether you should start to break the sql query in question up, into 2 queries maybe?

I have the problem at the moment where I am pulling data from 5 different tables, subqueries and all! I am just not entirely happy with the size of the query and wanted to hear other opinions, Do you try to simplify the query by breaking it up?

All tables are normailised (to the best of my ability) and index accrodingly so all that is ok, I am just curious to hear anybody else's thoughts on this.

Table Query Taking WAY TO LONG
I have a query that is inner joined with another table based on country codes
Select distinct Name.* from Name inner join Location on Location.key =
Name.key and Location.cc in ('<list of countries here>');

The problem is when I have more than 2 country codes, the query takes forever...
When I show processlist, it says converting HEAP to MyISAM ( this takes over an hour )...
I've tried bumping up max_heap_table_size to 128M and tmp_table_size to 128M, but that only
seems to delay the converting HEAP to MyISAM message....

Name contains about 3 million records and Location contains about 1.5 million records. Code:

Error Pasting Long Query Into Terminal
mysql database < /location/of/statement.txt

Is there a limit to the number of lines a statement can have in a terminal window?

OK, so I just tried the same query on another server and it works in that terminal window? Is there a clip board or cache that needs to be cleared from mysql every once in a while? Why would it work on one and not on another.

How Do You Cancel Out Of A Long Running Query W/out Exiting Mysql?
How does one cancel out of a long-running query that has been entered at the command line w/out exiting completely out of the mysql interface?

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.

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?

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

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.

Query Time
Is there a function out there to determine the length of time a particular query took

Time Query - Please Help?
Iâ€™m completely baffled by a query that I thought I had working yesterday, but I canâ€™t get it working today.

Hereâ€™s the screnario:

Iâ€™m publishing a story to the web and I donâ€™t wonâ€™t it to be published live until:

-The current date (type: date) is less or equal to now
-The current time (type: time) is less or equal to now

So hereâ€™s my query:

select * from cms_stories
WHERE section = 'news'
AND published_web_date <= NOW() OR published_web_date IS NULL
AND (published_web_time <= NOW() OR published_web_time IS NULL)
ORDER BY story_id
DESC LIMIT 1
Now the query works for the published_web_date, but not for the published_web_time. It seems to ignore that as you can see from the screenshot returned below from that query.

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.

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?

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)?

Limiting Query Time
Is it possible to limit the query time in MySQL (3.x or 4.0)? For
example, I'd like to have any query that takes more than a specified
number of seconds just quit automatically. Seems dumb, but on a web
site, nobody is going to wait minutes for a query to return so they
refresh anyway. So on a busy server, MySQL ends up with several queries
running that all take a long time to finish which compound to make it
even slower. A simple time limit would solve the problem. Yes, I know
that the queries should take less time, but again, on a busy server,
sometimes the longer queries do take a long time (10 minutes or more)
to complete.

Query Execution Time
I m using inner join where there is no keys (Primary and foreign). I want to know that Does key affects execution times?

Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code:

select
date,
`time` as timetime
, time_format(`time','%l:%i %p') as Printtime,
client,
address
from..........

Exceeded Run Time Query
I made a application in PHP, I have a table "control1" and this table have 85000 registries and Size is 200 MB, so when I try do a query in PHP, I have a error "exceeded run time", I changed the time but is the same, and after I connected by console, and I could see that the query take 14 minutes.

Here my sql:

$sql_filtro="SELECT COUNT(*) nFilas FROM control1 WHERE MATCH (contenido) AGAINST ('$q')";

How To Get Time Execution Of A Query?
Do you know how to get the time that a query executes? Is there a formula on how to get it?

Example:

Note: I have 5000 data in the table cars

$result = mysql_query("Select * from cars");

Question: How to get time of executing the query above?

How To Get Query Execution Time
How to get query execution time in "mysqlquery.log" files

I am using:

Linux 7.3
mysql 4.1

How to display the query execution time for each and every query in log files.
i have configured "slow-log-query" and "mysqlquery.log" in "my.cnf" is pasted below.

log = /var/lib/mysql/mysqlquery.log
log-slow-queries = /var/lib/mysql/slowquery.log
long-query-time = 0
slow-launch-time = 1

then i can get a output in "mysqlquery.log" like,

070830 9:24:29 2 Connect root@localhost on
2 Init DB jbdatabase
2 Query select count(*) from vacancy1_table
2 Query select category,listcategory from category_t
able order by category
2 Quit

but i need to display the "timetaken of each end every executed query" along with the query in the above logfile like,

E.g:select category,listcategory from category_table order by category(0.03 secs).

How To Get A Query Execution Time?
How to get a query execution time?

Execute Time Of A Query
I'm running a SELECT * query on a table with over 3,000 rows. I need to know the execute time on the query.

PHPMyAdmin shows it to me, but because they automatically add a LIMIT 0, 30 I suspect the time is only for those 30 records. It also has a feature to prevent web server timeouts that prevents me from setting the LIMIT to 0, 3100.

BTW, I'm programming in PHP. Does the time to run the query get automatically passed when PHP sends the query to MySQL?

<? PHP
$Query = "select * from my_table where 1";
$Result = MYSQL_QUERY($Query);
?>

Last Query Execution Time
Is there a way to retreive the elapsed time for the prevously executed query?

Alternatively, Is there a way to query the current time in fractions of a second?

I am attempting to use a stored procedure to execute and track the time it takes to run some queries and other commands.

The logic of the stored proc would go something like this.....

Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this.

Here is the query im using:

Time Query Question
I'm trying to pull dates from a db that are newer than 6 months old. I've been testing with 1 day, since there's nothing in the db that's older than 6 months.

query

PHP

$DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time ";
$DATEsql .="FROM login ";
$DATEaql .="WHERE time_in > CURDATE() - INTERVAL 1 DAY ";
$DATEsql .="ORDER BY time DESC ";

that is giving me the same results as

PHP

$DATEsql = "SELECT DISTINCT DATE_FORMAT(time_in, '%m/%e/%Y') as time ";
$DATEsql .="FROM login ";
$DATEaql .="WHERE time_in < CURDATE() - INTERVAL 1 DAY ";
$DATEsql .="ORDER BY time DESC ";

This is what I'm interpreting as what I need to do according to the MySql manual.

Query Log With Execution Time?
I have developed a big property portal web site and have probably written about 1000 different SQL queries in the process (OK, maybe only 500 or so) but a lot.

The site is now gaining popularity and although it is on a dedicated server I want to make sure that everything is running smoothly. I have optimised several tables with indexes etc. and have sped up certain tasks, but would like to know if there are any other queries that take too long and should be optimised.

Is there a way to log all queries together with the time it took to execute the query? Is this already logged and if so - where is it?

Query Execution Time In PHP Script
I am interested in displaying the query execution time as mysql does from the console, but using php. I've looked everywhere for this and can't find anything. Does anyone know how to display this information to the browser via php? The information must be there since the console provides it.

I checked the php function mysql_info(), but that only shows records, duplicates, deleted, matches, changed, warnings, etc. Didnt see any specific php function for getting execution time.

How To Query For Multiple Records At The Same Time
Basically let us assume i have a table (table1) with column (column1). I want to select only the records that have column1='x' or 'y' or 'z'. Is it possible to create a standard query that will do that for any number of records?

Time Zone For Query Browser?
My server time is set to CST. phpMYSQLadmin shows CST.
The website that the information is stored on is CST.

When I do a query from MySQL Browser, it's providing information back in GMT.

Does the browser undo timezone? It makes since on a corporate level, so that the time is always being pulled up correctly.

So, my question is. If all my servers and sites are all set to CST, do I still need to do queries with a time zone change in it, CST?

TIMESTAMP :: Query By Time And Date
I have a field in my table that holds a timestamp. Below is a pseudo-query to describe what I would like to do.

SELECT * FROM `mytable` WHERE `saved_date` <= `6/24/05 10:22:34 AM`

`saved_date` is the field holding a TIMESTAMP. If anyone could give me the query that would actually do what I am trying to do above,

Query Execution Time & Mysql_stat
I am using mysql for my shopping site,Performance of site very slow then I used mysql_stat php function to find out how many slow queries,it shows 20.

Now my question is any function in mysql(or)PHP to show 20 slow queries.

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?