Hi Bernd,
Bernd Jagla wrote:
Hi,
I have a problem with a very slow query that should be VERY fast.
> select * from tfbs_ecr t
[snip]
where ecr_id in
(301,302,316,323,345,346,351,362,363,364,380,411,413,451,464,466,470,471,481
,498,506,507,538,541,543);
=> returns 1167 rows within a few mil
Hi,
I have a problem with a very slow query that should be VERY fast.
There are two tables:
CREATE TABLE `tfbs_mm`.`tfbs_ecr` (
`tfbs_id` int(10) unsigned NOT NULL,
`ecr_id` int(10) unsigned NOT NULL,
KEY `tfbs_id` (`tfbs_id`),
KEY `ecr_id` (`ecr_id`)
) ENGINE=MyISAM DEFAULT
ry runs slow.
>
> The question I have is, why is the index not used on larger-range
> queries? Could it be because the key_buffer_size parameter is too small?
> Presently, it's set at the default value of 8M. Could increasing that
> value to, say, 128M, potentially solve this p
I have is, why is the index not used on larger-range
queries? Could it be because the key_buffer_size parameter is too
small? Presently, it's set at the default value of 8M. Could
increasing that value to, say, 128M, potentially solve this problem?
Or is there some other factor inv
Hi, Bill Easton wrote:
> I think that MySQL is doing what would be expected, namely an index scan
> which reads entire index on IP and selects distinct values of IP.
What I expect it to do is to give me all the distinct values in that
row. It should be able to notice that there are only a few dis
Matthias,
I think that MySQL is doing what would be expected, namely an index scan
which reads entire index on IP and selects distinct values of IP. Whatever
you do, it's going to read 10,991,123 of something and select distinct
values. The use of the index saves a possible sort and allows readi
Hi,
gerald_clark:
> > KEY `Trap` (`IP`,`Type`,`epoch`),
> > KEY `IP` (`IP`)
> >
> Your second key is redundant.
True (there's a UNIQUE missing in there).
Deleting the first key, however, doesn't change anything.
--
Matthias Urlichs | {M:U} IT Design @ m-u-it.de | [EMAIL PROTECTED]
--
Matthias Urlichs wrote:
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned N
[snip]
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate
latin1_german1_ci NOT NULL default 'WARN',
`epoch` int(10) unsigned NOT NULL defaul
We have a slight opimization problem here.
Given this table:
CREATE TABLE `test` (
`IP` varchar(15) collate latin1_german1_ci NOT NULL default '',
`Type` enum('WARN','ERROR','FATAL','UNKNOWN') collate latin1_german1_ci NOT NULL
default 'WARN',
`epoch` int(10) unsigned NOT NULL default '0',
On 15 Nov 2001, at 22:32, Carsten H. Pedersen wrote:
> > Thanks for the response, but I think you missed the key
> > collection_field_value right under the primary key above.
>
> You're right -- Sorry. Have you tried COUNT'ing something
> other than *? i.e. document_id or resource_id?
I tried
> On 15 Nov 2001, at 21:59, Carsten H. Pedersen wrote:
>
> > > PRIMARY KEY
> > > (`collection_id`,`document_id`,`tcml_field_id`,`order_number`),
> > > KEY `collection_field_value`
> > > (`collection_id`,`tcml_field_id`,`value`(12)),
> > > KEY `value` (`value`(12)),
> > > KEY `resource_
On 15 Nov 2001, at 21:59, Carsten H. Pedersen wrote:
> > PRIMARY KEY
> > (`collection_id`,`document_id`,`tcml_field_id`,`order_number`),
> > KEY `collection_field_value`
> > (`collection_id`,`tcml_field_id`,`value`(12)),
> > KEY `value` (`value`(12)),
> > KEY `resource_id` (`resource_i
> I'm trying to keep a table of summary statistics for another table,
> because I expected that generating the counts would take a while.
> But I never expected the query to take hours, as it does. On
> examination, I see that the query is not using an index, even
> though the table has an index
I'm trying to keep a table of summary statistics for another table,
because I expected that generating the counts would take a while.
But I never expected the query to take hours, as it does. On
examination, I see that the query is not using an index, even
though the table has an index on the th
On Fri, Feb 16, 2001 at 10:56:57PM +0100, Benjamin Pflugmann wrote:
>
> > When you set up your database server so that it has enough RAM to
> > keep the whole database in main memory (buffer cache), random reads
> > don't need to wait for physical disk seeks and can be very fast.
> [...]
> > So m
Hi.
On Fri, Feb 16, 2001 at 09:42:02AM +0100, [EMAIL PROTECTED] wrote:
> Benjamin Pflugmann wrote :
[...]
> > "Second" is never the best way. MySQL uses "First" up to some
> > percentage of the amount of records (30% I believe), then uses
> > "Third", because reading the data in file order (unsor
Benjamin Pflugmann wrote :
>
> Hi.
> []
>
> If MySQL uses the index, it has one of two possibilities.
>
> First:
> 1. Read the whole isbn index, and for each row
> 2. seek the record in the data file and get author from it.
> This is quite slow because of the random file seeks needed.
>
>
Hi.
On Thu, Feb 15, 2001 at 03:53:39PM -0500, [EMAIL PROTECTED] wrote:
> 15/02/01 15:14, Quentin Bennett, [EMAIL PROTECTED]:
[...]
> > Adding the author to the query means that the query now has to go to the
> > data file to get the information, and since there is no restriction on isbn,
> > the
15/02/01 15:14, Quentin Bennett, [EMAIL PROTECTED]:
> Hi,
>
> I think you are mis-understanding the information provided. What the "using
> index" note means is that the isbn column is part of the index, and since
> that is all that is selected, the index file is used to provide the data,
> the
s, is
the result pre-sorted, so the order by is trivial, or is a sort still
performed.
Hope this helps
Regards
Quentin
-Original Message-
From: François Bélanger [mailto:[EMAIL PROTECTED]]
Sent: Friday, 16 February 2001 08:01
To: [EMAIL PROTECTED]
Subject: Index not used for order by wh
Hi,
I'm having a hard time figuring why mySQL does not use the index when
sorting a table without any where clause when I include more than 1 field in
the select part. To resume the case, I created a temp table with 3 single
fields, added an index (see bottom for more details), ran 2 queries with
22 matches
Mail list logo
