MYSQL :: Easy SELECT With OR Takes A Very Long Time

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 !

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 !

Easy Way To Get Time Zone Offset
I need to get the standard offset value from GMT for any given time zone name in the mysql.time_zone_name table. Is there an way in MySQL to do this?

For example:
I have 'US/Eastern' time zone. I know that the value I'm looking for is -5. How can I get this out of MySQL. I don't care about DST offset, just standard time offset.

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 !

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 !

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 Replies !

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!

View Replies !

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.

View Replies !

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.

View Replies !

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

View Replies !

Select Row From Table, EASY
I'm drawing a blank and can't find it my Sams 24 hour book.

I've got a player name and stats in a DB so a row in the database would look like
Chris Brown 300 1200 5 40 325 2

I need to select(display PHP) Chris Brown and everything associated with him.

I had this all setup and haven't thought of it in years and now....geesh I just feel like an idiot as nothing I've tried works and I know it's connect to DB then one line of code, a select statement.

View Replies !

Another Easy(for You, Hard For Me) Select/join
Table 1 indexed on ID.
Table 2 is a chat log. Colums: From, To

I'd like to find out who is NOT chatting.
So, I'd like to select all ID in Table1 that are NOT found in either column From or To in Table2

So far I'm using two selects, but this doesn't seem right.

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 !

Long Blob Select
select * from s_proc where content = 'asiufhasiu'
the scrambling field was copied from the control panel of the binary field.
Is it possible to do straight query on the control panel directly against
the database/tables?

View Replies !

How Check How Long Did My Select Took ?
I have a client for mysql which give me the time it took to query but now is very doubtful. I have 2 machines on the same network accessing the same database one is showing 360ms and the other is showing 5469ms on the same select? Thus I am very confuse where can I verify which is the exact time ? Thanks.

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 !

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 !

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

View Replies !

Select Between Date And Time
I have field date and time respectively. I want to select date between (date1 and time1 > 17) and (date2 < 17)

I tried two ways but doesn't work, please see code below.
SELECT * from purchases_suppliers WHERE (date_invoice >= '2009/02/25' and time_invoice > 17) AND (date_invoice <= '2009/02/27' AND time_invoice < 17)

SELECT * from purchases_suppliers WHERE date_invoice between ('2009/02/25' ANd time_invoice > 17) AND ('2009/02/27' AND time_invoice < 17 )

View Replies !

Update And Select Same Time
Code:

UPDATE art SET art.art_writer = (SELECT writer.writer_name, art.art_author FROM writer, author WHERE art.art_author LIKE 'writer.writer_name')

Is this query syantex is ok ?

Where, art_writer is empty fild where I want to put correct value which is in writer table and it should similar to value in art_author filed.

View Replies !

SELECT With TIME INTERVAL
I have in my database a table with this structure:

id, latitude, longitude, speed, userid and Timestamp....

View Replies !

Easy Query...probably
I've got three tables:

- events
- tasks
- docs

Events are made up of one or more tasks and tasks may or may not have docs.

What I need to do is get all the events with associated tasks and docs.

View Replies !

QUICK AND EASY ONE
im fairly new to php/mysql so please bear with me
I am trying to upload a txt.file created from an excel spreadsheet using the following query:

LOAD DATA INFILE 'path/file.txt' INTO TABLE my_table;

im getting a syntax error!
what could be wrong? are there any other attributes i should be adding to this query? i also tried uploading a csv version of the same file and get the same error.

View Replies !

Help With A (probably) Easy Query
I am trying to write a query to run through phpmyadmin. What I have is this:

In table "vb_user" I want to pull the data in field "username" and copy that username in another table called "vb_userfield" in a field called "field5". Both tables share the field "userid" which is how the relation is made. I know this is probably very simple but man... I just can't seem to pull it off.

I've got about 500 members and I really want to populate that field "field5" with thier username without having every one of them doing it manually.

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 !

Select Statement Where Time <= 5min Ago
i tried many different MySQL fucntions and looked through the MySQL manual, and it didnt help me any. What i want to do is select all rows from a table where my time column(DATETIME) is less then or equal to 5 minutes ago from now().

View Replies !

Cron Job And Time Select Question
I am trying to figure out a select...where statement to use in a script running via cron job several times an hour.
example in plain english:cron job runs hourly, e.g. 12:50, 1:10, 1:30
need to select records where a datetime field is > 12:45
next hour I need to select where the datetime field is > 1:45
and so on....How do I calculate the time to use in my where statement?

View Replies !

Select Date And Time In Same Column
I have table called mytable and there I have column named time
example:
time
2006-07-10 10:28:06
2006-08-18 20:48:22
2006-09-15 12:11:41
2006-10-12 23:06:02

is there any possibles that I can make query example:
SELECT *FROM mytable WHERE date BETWEEN 2006-07-10 AND 2006-09-15 AND time
BETWEEN 10:28:06 AND 12:11:41

My point is can I make query where I first find between date and after that I make
query where I find between time when information is in the same column?

View Replies !

Select Records That Only Occur One Time
SELECT records that only occur one time.

I have a table with an ID field. I want to SELECT the records where the ID field only has one occurrence. If it appears in two or more rows I do not want to SELECT it.

View Replies !

Select Date Ignoring Time
Im using a datetime fields eg. "2006-08-07 16:01:09" and I want to select dates... eg. "select * from mytable where mydate="2006-08-07" ignoring the time.

View Replies !

Easy Collation Changing.
A while ago, I messed around with collations etc, without understanding the rammifications.Ended up with some tables swedish, some general and others, but all were under Latin1. After some time a few queries started breaking. Anyway, I played and tinkered and eventually managed to fix.
The problem was that I couldn't go and Alter thousands of columns manually, so a PHP script was created, and works fine.
But for the sake of knowledge, is there a way to change the Character Set and Collation stuff of an entire database(s) quickly and easily using SQL?

View Replies !

Create User Easy?
Is there an easy way I can just create a MySQL User... and give him the privledges to only do things to databases that he from now on creates..?

Or is it gonna be every time I create a database I would have to give him privledge to that database? What type of route would I have to go with that...

Also when his user connects to the MySQL Server is there a way I can do a SHOW DATABASES and show only the databases that he has created? He needs to also be able to Create/DELETE/APPEND/UPDATE any database that he creates.

What's the easiset way about going around this route? Am I going to have to Grant him Permissions on every database that he creates? Does the root account have to grant the permissions?

View Replies !

I Think It's An Easy To Fix Sql Syntax Problem
can anybody help me :

@mysql_query("INSERT INTO leden WHERE gebruikersnaam = ".lekubb($_POST['gebruikersnaam'])." (status) VALUES (Global Admin)") or die(mysql_error());

Ik get this error :
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 'WHERE gebruikersnaam = t (status) VALUES (Global Admin)' at lin

You can see the script online at http://www.webmasterhelp.be/systeem/testje.php
When you fill in something and press enter you'll see the error.
If you need more info or something you can mail me at arne_dejongh2@hotmail.com or post something here.

View Replies !

Easy Way To Delete Duplicates?
I had 2 membership db's. One had all the members (65K) db2 had all the members that have registered on the website. I am wanting to consolidate the two.

Basically i took both, and merged them together into their own db. now the membership list is about 80k. 20k or so of that are duplicates. My idea was to export the thing as .xls and remove the duplicates via Microsoft Excel. However, Excel has a limit of 65Kish rows. so i can't really go about that.

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 !

Datatype TIME - Select Statement Format
The table I have has two columns of ID and INTERVALS. The INTERVALS is datatype TIME. Whenever I do a select I always get the table back showing the time in the format HH:MM:SS like for 11am I get back 11:00:00. I only want HH:MM like 11:00. How do I achieve this? I have tried to limit to 2 decimal places but that has not worked so far. ...

View Replies !

Select Rows From Date Not Time In Where Clause
I am trying to select all the rows that where entered today and I have a datetime type column to store the date data in. How can I specify in my where clause to select where datecol=today and not have to specify the time? If I do where datecol=<?php date('Y-m-d'); ?> its not working

View Replies !

Select Record In Time Range Not Working
$today=date('Ymd');

"SELECT * FROM data WHERE (startdate>='".$today."' AND stopdate <='".$today."'.....

View Replies !

Sql Virgin. An Easy Question For U Experts Im Sure
Im a database virgin, i would like to know more but find it quite confusing, although im sure its not!

Im setting up a cms and have installed wamp on my machine and its working. Now the cms has asked me to create a sql database so ive gone to php my admin and put a name in the creat field and created it. Its showing in the left field of phpmy admin.

Now the cms is asking for the name of mysql login and mysql passwoord!

Where the hell do i find that? ive never created one!

View Replies !

Easy Question Re: Type Sizes
Just wondered, what exactly does:
int(11) do
does that mean you could store UP to an 11 digit number in the field?
ie. 01234567890
with the highest possible number:
99999999999

and does the same apply to tinyint and all the other int types?

if that is the case, why would one chose tinyint(1) over int(1)?

and also I guess the same goes with text/varchar types.

View Replies !

Quick And Easy Update Query? Or Not Possible?
I need to do a global change to a column in my database. I know this is poor planning on my part, but I recently made a change to all of my image extensions for speed purposes.

I have a column called thumbnail_path, and every value ends with .GIF. I would like to change them to all end in .JPG. How can I do this? Is this even possible to do in one update query? or will I need to select, parse w/ php and then update?

View Replies !