Hello Larry,
On 2/4/2015 3:37 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green 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
wrote:
Hi Larry,
On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries.
On Wed, Feb 4, 2015 at 3:25 PM, shawn l.green 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
>> wrote:
>>>
>>> Hi Larry,
>>>
>>>
>>> On 2/1/2015 4:49 PM, Larry Martell wrote:
I have 2 queries. One takes 4 hours
Hi Larry,
On 2/4/2015 3:18 PM, Larry Martell wrote:
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green 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 a
On Wed, Feb 4, 2015 at 2:56 PM, shawn l.green 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 main table being
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 i
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 s
On 2/7/10 7:28 AM, "Anthony Urso" 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 (usin
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
To
>
>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 ther
f you 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 mod
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 seem
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 al
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 s
att.net
> To: dstepli...@gmail.com
> CC: mysql@lists.mysql.com
> 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, 200
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® wrote:
> > I'm having random query slowness that i can only reproduce once. My main
> > question is that the query runs
Can you show us the output of DESCRIBE score and SHOW INDEX FROM score?
On Wed, Jul 15, 2009 at 6:44 PM, Tachu® 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 query cache enabled he
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 (0.5
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 think
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 syntactic
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 applic
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
encry
-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 per
-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 worki
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
w
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:http://lists.mysql.com/[EMAI
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
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
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 i
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 FU
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] wrot
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
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
> appli
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 able
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. I
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 u
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 o
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 '',
> `ipad
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 N
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 N
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 N
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 qu
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:
http://de
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. Alt
[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%
>
^ Does not look good to me !!
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 ano
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]
>
> >
> > Th
[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
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
> 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 differe
ke 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
le definitions produce different
results 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
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/200
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
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&#x
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
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.mysq
On Fri, 10 Sep 2004 05:54:42 +1000, Matthew Boulter <[EMAIL PROTECTED]> wrote:
> 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 see
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
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' a
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
> 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,
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)
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
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
> >
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 star
It may be just how MS does things from
one OS to another. Then again it may 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) natur
> 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 fir
>
> - Original Message -
> 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
>
>
> >
king to the SCO boxes.
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.
-
B
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 interest
OTECTED]>
Sent: Friday, October 05, 2001 1:34 PM
Subject: RE: Bizarre query performance
> > Have you tried "explain"ing the two select to see where all the time is
> > being spent and how the queries a
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 b
> Have you tried "explain"ing 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 | key
1 1:18 PM
Subject: Bizarre query performance
> 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
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
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
