On Fri, Aug 22, 2003 at 12:42:27PM -0700, Steven Roussey wrote:
>
> But since this data is read only, why not reformulate the data for the
> queries you are going to make? This is the opposite of normalizing, and
> will require more disk space, and is not flexible, but it will be fast.
> Of course
After looking over your results, I would keep the dir1 index at least on
the first and last table.
But since this data is read only, why not reformulate the data for the
queries you are going to make? This is the opposite of normalizing, and
will require more disk space, and is not flexible, but i
On Fri, Aug 22, 2003 at 10:07:46AM -0700, Steven Roussey wrote:
> Hmmm, just in case you can't change the table layout...
>
> Run this through MySQL. First I get rid of the other index I made, then
> add chained indexes so there is no need for data file lookup. Also, one
> direction of the query t
On Fri, Aug 22, 2003 at 09:03:55AM -0700, Steven Roussey wrote:
> > All the indexes were single indexes, partly because I haven't
> > yet made the effort to understand composite index. I guess it's
> > time ;-).
>
> Oh.
>
> There are better places to start than this list. ;) The manual can be a
>
Hmmm, just in case you can't change the table layout...
Run this through MySQL. First I get rid of the other index I made, then
add chained indexes so there is no need for data file lookup. Also, one
direction of the query table join chain was not always using the indexes
for the where. One direct
> All the indexes were single indexes, partly because I haven't
> yet made the effort to understand composite index. I guess it's
> time ;-).
Oh.
There are better places to start than this list. ;) The manual can be a
great starting place, and several people on this list have written books
about
On Fri, Aug 22, 2003 at 07:33:56AM -0700, Steven Roussey wrote:
> > Here's the CREATEs, somewhat edited to remove parts not relevant
> > to this discussion, to save space:
>
> I never actually looked at your JOIN statement more than a quick
> glimpse, but I will (though not just right now). Before
On Fri, Aug 22, 2003 at 10:23:37AM -0400, Keith C. Ivey wrote:
> On 21 Aug 2003 at 21:38, Jesse Sheidlower wrote:
>
> > Huh, I was told the exact opposite, that if most of the
> > entries are smaller than the maximum length of the field, you
> > should use an index about the size you expect most e
> Here's the CREATEs, somewhat edited to remove parts not relevant
> to this discussion, to save space:
I never actually looked at your JOIN statement more than a quick
glimpse, but I will (though not just right now). Before I do, can you
try this (I still don't have data or I'd play with it mysel
On 21 Aug 2003 at 21:38, Jesse Sheidlower wrote:
> Huh, I was told the exact opposite, that if most of the
> entries are smaller than the maximum length of the field, you
> should use an index about the size you expect most entries to
> be. Why would you ever use a shorter index than the full colu
On Thu, Aug 21, 2003 at 06:58:29PM -0700, Steven Roussey wrote:
>Jesse Sheidlower wrote:
>
> > Hmm. When I returned to the multiple-table query that started
> > this thread,
>
> And it was slow. Yeah, one thing at a time. It makes it easier for
> people reading this list now or in the future (if
> GOD! OK, sorry, I wasn't quite expecting this:
> Wow!
:)
>
> But what's the explanation for this huge improvement? Again, I
> was always told the opposite, and the Manual itself says: ...
Yes, and it is true (usually). But your EXPLAIN showed a filesort and
that is bad. What happens is that i
On Thu, Aug 21, 2003 at 04:58:47PM -0700, Steven Roussey wrote:
> > No, the contents can be of mixed case. Where does that leave things?
>
> **Index the length of the entire column.** It then should not need to
> have to do the filesort. Actually the binary option would not have
> really helped. T
> No, the contents can be of mixed case. Where does that leave things?
**Index the length of the entire column.** It then should not need to
have to do the filesort. Actually the binary option would not have
really helped. The explain should say 'Using Index'. Get back to me on
this and tell me th
On Thu, Aug 21, 2003 at 03:36:54PM -0700, Steven Roussey wrote:
> > Executing just the search on the word table, with no joins to the
> > table with the dates, is still slow:
>
> Then it is not worth while to focus on anything else until you fix that.
> Are the contents of this field always in low
Can you post your DDL to go along with your DML?
-Original Message-
From: Allen Weeks [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 4:51 PM
To: Jesse Sheidlower; Cybot
Cc: [EMAIL PROTECTED]
Subject: RE: Slow results with simple, well-indexed query
The only thing I can add is
The only thing I can add is check you hardware and OS platform.
Cheers
-Original Message-
From: Jesse Sheidlower [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 21, 2003 11:44 AM
To: Cybot
Cc: [EMAIL PROTECTED]
Subject: Re: Slow results with simple, well-indexed query
On Thu, Aug 21
Cybot wrote:
> Jesse Sheidlower wrote:
>> An example of a query is to get all the words (the cg.cw
>> field) in a particular alphabetical range that have been
>> added in some timespan (the sref.cd field). The cg table
>> has about 3M rows, and the sref table about 70,000; the
>> intervening tables
On Thu, Aug 21, 2003 at 06:01:31PM +0200, Cybot wrote:
> Jesse Sheidlower wrote:
>
> >I'm struggling with speed issues on some queries that
> >I would have expected to be relatively fast. Perhaps
> >even more frustratingly, when I've tried to break
> >these down into their components, they still
On Thu, Aug 21, 2003 at 05:59:54PM +0200, Mechain Marc wrote:
>
> What is the value of "sort_buffer_size", may be you could
> increase the value for having faster ORDER BY (all in memory
> intead of using temporary file on disk).
I had previously tried that--I sometimes have big GROUP BY
queries a
Jesse Sheidlower wrote:
I'm struggling with speed issues on some queries that
I would have expected to be relatively fast. Perhaps
even more frustratingly, when I've tried to break
these down into their components, they still execute
very slowly. I've looked over all the relevant suggestions
for
What is the value of "sort_buffer_size", may be you could increase the value for
having faster ORDER BY (all in memory intead of using temporary file on disk).
Marc.
-Message d'origine-
De : Jesse Sheidlower [mailto:[EMAIL PROTECTED]
Envoyé : jeudi 21 août 2003 17:34
À : [EMAIL PROTECTED
On Thu, Aug 21, 2003 at 11:34:00AM -0400, Jesse Sheidlower wrote:
>
> I'm struggling with speed issues on some queries that
> I would have expected to be relatively fast. Perhaps
> even more frustratingly, when I've tried to break
> these down into their components, they still execute
> very slow
23 matches
Mail list logo
