Hi Larry,
On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching
Hi Larry,
On 2/4/2015 3:18 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
Hi Larry,
On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where
On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
Hi Larry,
On 2/4/2015 3:18 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com
wrote:
Hi Larry,
On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries. One
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
Hi Larry,
On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The
Hello Larry,
On 2/4/2015 3:37 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green shawn.l.gr...@oracle.com wrote:
Hi Larry,
On 2/4/2015 3:18 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green shawn.l.gr...@oracle.com
wrote:
Hi Larry,
On 2/1/2015
I have 2 queries. One takes 4 hours to run and returns 21 rows, and
the other, which has 1 additional where clause, takes 3 minutes and
returns 20 rows. The main table being selected from is largish
(37,247,884 rows with 282 columns). Caching is off for my testing, so
it's not related to that. To
Does the order of tuples in a bulk insert impact later query
performance? E.g. will sorting the rows before a bulk insert cause
queries to perform better for indexed or non-indexed fields?
Thanks,
Anthony
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
On 2/7/10 7:28 AM, Anthony Urso antho...@cs.ucla.edu wrote:
Does the order of tuples in a bulk insert impact later query
performance? E.g. will sorting the rows before a bulk insert cause
queries to perform better for indexed or non-indexed fields?
when i load a large body of data (using
Any ideas of what could be causing the varied response time on a simple
query
when everything on the server appears to be identical from one run to
another?
Are there settings that can be made on the server to tweak response time
for a
database/query like this?
[JS] Is it possible that there are
I have a moderate sized database set up and a program that allows users to
create ad-hoc queries into the data based on entries in a form, so that I, as
the programmer, have control over the actual construction of the queries and
can do what is needed to optimize queries. I also keep a log of
In the last episode (Aug 13), Leo Siefert said:
After playing around with the query in PhpMyAdmin I am totally perplexed
as to what could be causing the problem. Sometimes the query will execute
in less than 30 seconds, but other times it takes from 4 to 10 or more
minutes. It never seems
get consistent times? What about running this directly through
the mysql cli?
Regards,
Gavin Towey
-Original Message-
From: Leo Siefert [mailto:lsief...@sbcglobal.net]
Sent: Thursday, August 13, 2009 1:10 PM
To: mysql@lists.mysql.com
Subject: Erratic query performance
I have a moderate
On Wed, 15 Jul 2009 23:53:05 -0400 Darryle Steplight said:
Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
On Wed, Jul 15, 2009 at 6:44 PM, Tachu®tachu1+my...@gmail.com wrote:
I'm having random query slowness that i can only reproduce once. My main
question is that
Subject: Re: Slow query Performance
On Wed, 15 Jul 2009 23:53:05 -0400 Darryle Steplight said:
Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
On Wed, Jul 15, 2009 at 6:44 PM, Tachu®tachu1+my...@gmail.com wrote:
I'm having random query slowness that i can only
In the last episode (Jul 15), Tachu(R) said:
I'm having random query slowness that i can only reproduce once. My main
question is that the query runs faster the second time around but i dont
have query cache enabled here is some info from mysql profiler;
The time is spent mostly on the
I'm having random query slowness that i can only reproduce once. My main
question is that the query runs faster the second time around but i dont
have query cache enabled here is some info from mysql profiler;
The time is spent mostly on the sending data step
first time around
63 rows in set
Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
On Wed, Jul 15, 2009 at 6:44 PM, Tachu®tachu1+my...@gmail.com wrote:
I'm having random query slowness that i can only reproduce once. My main
question is that the query runs faster the second time around but i dont
have
Auch, thanks for pointing that out, what a terrible mistake.
I am aware of the performance issue, and so is the customer. But with a
table that's only going to hold maximally 60.000 records in 10 years,
I'm not afraid it'll cause significant problems. If it gets out of hand
we'll have to
I put it here:
http://pro.datisstom.nl/tests/bench.tar.bz2
The encryption isn't really a *real* security measure, except for when
somebody is stupid enough to install phpMyAdmin or anything equivalent
and try to get personal data. The problem is the password needs to be
anywhere on the
Hi John,
OK, no conspiracy here. Here is your problem:
25 $qry = sprintf(SELECT id, line FROM `encryptietest`
WHERE AES_DECRYPT(`field`, '%') LIKE '%%%s%%', $enckey, $word);
You are missing the s in %s for your first string argument, which
causes the query to be syntactically
Dear you,
I've been working on encrypting some data for a customer. They want
their personal/sensitive information encrypted in the database, but they
want to be able to search it too, through the application. So we've been
thinking a bit, and just started trying and benchmarking some solutions
-marche.com
-Original Message-
From: John Kraal [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 23, 2007 8:51 AM
To: mysql@lists.mysql.com
Subject: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
Dear you,
I've been working on encrypting some data for a customer
-Original Message-
From: John Kraal [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 23, 2007 8:51 AM
To: mysql@lists.mysql.com
Subject: Query performance plain/text versus AES_DECRYPT(): LIKE %..%
Dear you,
I've been working on encrypting some data for a customer. They want
their personal
Hi John,
Your attachment for the php code got stripped somewhere. Can you post
it somewhere (http preferable)? In either case it's going to result in
a full table scan, so they are actually both a bad strategy long term,
but they should in theory perform as you would expect, with with
Thanks a lot!! :D
You were right. There was a bug. Upgrading to mysql 4.1.20 solved my
problem.
Daniel da Veiga wrote:
Check http://bugs.mysql.com/bug.php?id=12915
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
Hi, List!
I'm a little bit confused with (IMHO) poor query performance.
I have a table with 1'000'000 records.
Table consists of 2 service fields and a number of data fields. Service
fields are status and processor_id (added for concurrent queue processing).
The question is why are updates
On 6/6/06, Eugene Kosov [EMAIL PROTECTED] wrote:
Hi, List!
I'm a little bit confused with (IMHO) poor query performance.
I have a table with 1'000'000 records.
Table consists of 2 service fields and a number of data fields. Service
fields are status and processor_id (added for concurrent
Hi Green,
Scrubbing out the data is a great suggestion, I will definitely try that
out. I did try out the other option using REGEXP instead of matching
individual conditions. It definitely cleaned up the implementation, but
did not really improve the performance.
-Harini
[EMAIL PROTECTED]
Hi Brent,
Using REGEXP did not really help with the performance. I need to do
whole word matching sowould prefer not to do LIKE '%Vice President%' as
it may return ome negative results.
I separated out some of the text based columns in to a different table
using MYISAM storage engine. Using
You're still doing a full table scan with REGEX, so you'll never get
it really fast. I was thinking it would be slightly faster because of
less comparisons. It's the full table scan and no use of indexes that
you want to get away from. Without doing that, the only way to get
things faster
Hi,
I am using MYSQL 4.1 database in my J2ee application. I am facing
performance issues with some queries that are being run on text fields.
Since MYISAM storage engine does not support transactions(and my
application requires the database tables to support transaction), I have
not been
Harini Raghavan [EMAIL PROTECTED] wrote on 10/04/2005
11:17:48 AM:
Hi,
I am using MYSQL 4.1 database in my J2ee application. I am facing
performance issues with some queries that are being run on text fields.
Since MYISAM storage engine does not support transactions(and my
application
Hello.
Does MYSQL provide any other option to perform text based searches? Can
someone suggest any tips for performance tuning the database in this
scenario?
Use the same queries linked with UNION instead of a lot of ORs in WHERE
clause. For example this query can't use index (at
James Tu wrote:
Hi:
Let's say I want to store the following information.
Unique ID - INT(10) autoincrement
First Name - VARCHAR (25)
Last Name - VARCHAR (25)
Age - INT(3)
In general 'age' is a bad column, because you need to know what year the
data was entered to calculate the current age.
Hi:
Let's say I want to store the following information.
Unique ID - INT(10) autoincrement
First Name - VARCHAR (25)
Last Name - VARCHAR (25)
Age - INT(3)
Date - DATETIME
Activity - VARCHAR(100)
Data - TEXT
I would be basing my queries on all columns _except_ the Data column. I.e. I
would be
Hi Folks!
Here, I have the following table:
CREATE TABLE `accesses` (
`time` varchar(15) NOT NULL default '',
`duration` int(10) default NULL,
`user` varchar(25) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL default '',
`result` varchar(30) default NULL,
`bytes` int(10) default
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED]
Hi Folks!
Here, I have the following table:
CREATE TABLE `accesses` (
`time` varchar(15) NOT NULL default '',
`duration` int(10) default NULL,
`user` varchar(25) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL
From: Fernando Henrique Giorgetti [mailto:[EMAIL PROTECTED]
Hi Folks!
Here, I have the following table:
CREATE TABLE `accesses` (
`time` varchar(15) NOT NULL default '',
`duration` int(10) default NULL,
`user` varchar(25) NOT NULL default '',
`ipaddr` varchar(15) NOT NULL
Fernando Henrique Giorgetti [EMAIL PROTECTED] wrote on 04/14/2005
02:34:30 PM:
Hi Folks!
Here, I have the following table:
CREATE TABLE `accesses` (
`time` varchar(15) NOT NULL default '',
`duration` int(10) default NULL,
`user` varchar(25) NOT NULL default '',
`ipaddr`
You could probably save a bit of processing time by changing:
concat(date_format(from_unixtime(time), %d/%m/%Y), - ,
time_format(from_unixtime(time), %H:%i))
to:
date_format(from_unixtime(time), %d/%m/%Y - %H:%i)
This would mean half the date conversions would be executed.
Separating out the
If you do I suggest you also include relevant table definitions and
possibly a little sample data (plus an indication of total table
sizes) and expected output, this will greatly assist anyone who my be
able to help. Oh yes, and don't forget to state the version of MySQL
you are running.
Coz
On
Can I post a query to this list and ask for help optimizing it?
--
Ryan McCullough
mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Thanks for the advice Steven, I'll bear it in mind and do some reading.
Graham
-Original Message-
From: Steven Roussey [mailto:[EMAIL PROTECTED]
Sent: 13 November 2004 02:52
To: 'Graham Cossey'
Cc: [EMAIL PROTECTED]
Subject: RE: Help with query performance anomaly
For production
It turns out that it appears to be a data discrepancy that caused the query
optimiser to, well, not optimise.
I thought the main table (r) with 3million records would be the problem, but
it was table p with 3100 records on the live server and 3082 records on my
dev pc that caused the problem.
For production systems, I would never let the mysql optimizer guess a query
plan when there are joins of big tables and you know exactly how it should
behave. Once you think a query is finished, you should optimize it yourself.
Use STRAIGHT_JOIN and USE INDEX as found here in the manual:
Hi
Can someone offer any advice on a strange problem I have at present...
If I run a certain query (see below) on my local development PC using
mysqlcc it returns in 3.7s.
If I run the exact same query on my live webserver (again using mysqlcc) I
have yet to get a result !!
Both databases have
What does EXPLAIN show for the query on both systems? (I am wondering if
you may have an index on your development system that you do not have on
your production server.)
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004
like this? Maybe something in the configs?
Thanks
Graham
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 16:28
To: Graham Cossey
Cc: [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
What does EXPLAIN show for the query
PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 16:28
To: Graham Cossey
Cc: [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
What does EXPLAIN show for the query on both systems? (I am wondering
if
you may have an index on your development system
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in key definitions?
Can 2 installations with the same table definitions produce different
Response at end
Graham Cossey [EMAIL PROTECTED] wrote on 11/11/2004 12:19:17 PM:
Thanks Shaun
EXPLAIN shows the same 'possible keys' for each table but 'key' and
'key-len' columns are different, as are the 'rows' as well of course.
I guess this points to a probable difference in
[big snip]
These are two different plans. Your development machine is using
the index
yr_mn_pc on the r table and is joining that table last. On your
production
server, the r table is joined second and is joined by the index PRIMARY.
Let me know how the ANALYZE TABLE I suggested in a
How do the OS statistics look on both boxes. Do top, sar, vmstat or
iostat show any CPU, memory or I/O performance issues? Does anything
odd appear in the /var/log/messages file?
-Jamie
On Thu, 11 Nov 2004 18:42:48 -, Graham Cossey
[EMAIL PROTECTED] wrote:
[big snip]
These are
!!
Comments?
Advice?
Thanks
Graham
-Original Message-
From: Jamie Kinney [mailto:[EMAIL PROTECTED]
Sent: 11 November 2004 19:25
To: Graham Cossey
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Help with query performance anomaly
How do the OS statistics look on both boxes. Do
[snip]
Have just run 'top' on the live server...
Before running the query I get:
13:56:09 up 45 days, 11:47, 1 user, load average: 0.00, 0.28, 0.44
24 processes: 23 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 0.0% user 0.0% system0.0% nice 0.0% iowait 100.0%
idle
Mysql 4.1.3
Windows XP SP1
All tables are InnoDB
The query (1):
select Product.id, LongAnswer.value, count(*)
from LongAnswer
inner join Answer on LongAnswer.answer=Answer.id
inner join QuestionDefinition on Answer.question=QuestionDefinition.id
inner join Survey on Answer.survey = Survey.id
inner
Haitao Jiang [EMAIL PROTECTED] wrote:
We want to test our MYSQL (4.1.4g) server's query performance, and I
just wondering if there is a tool that enable us sending a list of
queries over HTTP or JDBC repeatedly and gather/display the
statistics?
Honetsly, it's almost always better to write
Hi Haitao.
I'm in the process of developing one of these for the company I work for.
Feel Free to drop me a line and we'll see how we can get it going for you.
Regards
Ian
Haitao Jiang wrote:
Hi,
We want to test our MYSQL (4.1.4g) server's query performance, and I
just wondering if there is a tool
Hi,
We want to test our MYSQL (4.1.4g) server's query performance, and I
just wondering if there is a tool that enable us sending a list of
queries over HTTP or JDBC repeatedly and gather/display the
statistics?
Thanks
HT
--
MySQL General Mailing List
For list archives: http://lists.mysql.com
G'day all, I was hoping to leech from your amalgamated knowledge:
I've been asked to estimate the query performance of several SQL
queries that power our Reporting system. At the moment we're
preparing to scale up enormously the amount of data we're using in our
system, and therefore I'm trying
On Fri, 10 Sep 2004 05:54:42 +1000, Matthew Boulter [EMAIL PROTECTED] wrote:
snip/
Any help with the values I should be using or any guidance on
estimating a Queries Performance would be unimaginably appreciated.
This is an area in which I felt better armed when I used Oracle. I'm
curious to
I have a db that had some 20,000 records or so in it. I have a query to
find out how many jobs have been input during the current day. To add them
I ran the following query...
select count(idnumber) from maintable where inputdatetime '$date
00:00:00' and client='smith'
$date is the current
At 07:10 PM 1/23/2004, Larry Brown wrote:
I have a db that had some 20,000 records or so in it. I have a query to
find out how many jobs have been input during the current day. To add them
I ran the following query...
select count(idnumber) from maintable where inputdatetime '$date
00:00:00'
Hi,
I have a table with 18 million of rows. The table structure is
describe user_att
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| user_id | int(11)
2 index on this table:
- one unique index on user_id and att_id (pk)
- one index on att_id and user_id.
I need to have the following query:
select value from user_att where att_id = ? and value like '?'
(no wildcard)
1. when I do a explain, this query use the second index. But, if
I
Hi,
I was read from this group about LOAD DATA query performance. Someone was
tell me that when load large data text file into database which has primary
key or index may be slow. The way to make load data faster is drop and
create table without index, after load data then create
I have been testing an application that uses mysql on SCO OpenServer and I
have discovered some strange query performance. To investigate the matter
further, I have written a client program that uses the mysql C API directly
so that I can time things exactly.
Environment:
Server: SCO OpenServer
Have you tried explaining the two select to see where all the time is
being spent and how the queries are optimized?
--Russell
- Original Message -
From: Philip Brown [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, October 05, 2001 1:18 PM
Subject: Bizarre query performance
I
Have you tried explaining the two select to see where all the time is
being spent and how the queries are optimized?
Sorry, I should have included that in my detail.
+---+---+---+-+-+---+--+---+
| table | type | possible_keys | key |
On Fri, 5 Oct 2001, Philip Brown wrote:
Environment:
Server: SCO OpenServer V3.2 R5.0.5, AMD K6-2 350Mhz CPU, 128Mb RAM
mySQL: 3.23.39, compiled by me to avoid use of libraries, using latest
available pthreads
... much deleted...
Can anyone give me some assistance with this bizarre
Subject: RE: Bizarre query performance
Have you tried explaining the two select to see where all the time is
being spent and how the queries are optimized?
Sorry, I should have included that in my detail.
+---+---+---+-+-+---+--+---+
| table
In the last episode (Oct 05), Philip Brown said:
Server: SCO OpenServer V3.2 R5.0.5, AMD K6-2 350Mhz CPU, 128Mb RAM
mySQL: 3.23.39, compiled by me to avoid use of libraries, using latest
available pthreads
Clients: Win32 machines (more detail later).
There are 2 times I am interested in,
issues would affect general query performance.
However it does not explain the erratic (yet reproducible) nature of how
different queries perform badly, or well, depending.
-
Before posting, please check:
http://www.mysql.com
-
From: Philip Brown [EMAIL PROTECTED]
To: Russell Miller [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, October 05, 2001 1:34 PM
Subject: RE: Bizarre query performance
Have you tried explaining the two select to see where all
the time is
being spent and how the queries are optimized
I suppose your test program connects, and loops the same query multiple
times in the same session? (Just to rule out connect/disconnect
overhead)
Of course. I also run the same query multiple times, to eliminate caching
issues. Performance on successive iterations is the same as on the
not. Till you know that things are
exactly the same, you never really know.
All of these networking issues would affect general query performance.
However it does not explain the erratic (yet reproducible) nature of how
different queries perform badly, or well, depending.
That is what I
In the last episode (Oct 05), Philip Brown said:
What are your timings if you run your client on the SCO box? mysql
simply reports a query time of 10ms or less (0.01s). Of course, this
doesn't have any network overhead.
This rules out mysql as the cause for the delay.
I'd say start
On Fri, 5 Oct 2001, Dan Nelson wrote:
This rules out mysql as the cause for the delay.
I agree.
I'd say start dumping packets on the network.
I'd agree, but I'm confused as to why a different query (that
requests more data; 33 rows vs 1) can reliably execute and fetch in
10ms on all
Hi,
I am pretty new to mySql. (In fact just built a
databse and started working on it).
I want to do keyword search on a column (clob). It
may contain millions of records. How do I write query
so that it is efficient.
Thanks in advance for your time,
Puru.
79 matches
Mail list logo
