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 column
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
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 entries
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 I do,
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
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
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
great
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
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, it
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 slowly.
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]
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
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 as
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 execute
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 are all
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
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
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 lower
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
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. The
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 if
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 it
22 matches
Mail list logo
