- Original Message -
> From: "Tompkins Neil"
> Thanks for the information. One final question in what ways should we
> use EXPLAIN EXTENDED statement to help improve our query
> performance.
Explain is your friend. You should listen to it :-)
It gives a nice idea of how the database i
Thanks for the information. One final question in what ways should we use
EXPLAIN EXTENDED statement to help improve our query performance.
On Tue, Oct 11, 2011 at 2:51 PM, Johan De Meersman wrote:
> - Original Message -
> > From: "Rik Wasmus"
> >
> > Depends on the data and usage, but
- Original Message -
> From: "Rik Wasmus"
>
> Depends on the data and usage, but probably I'd go for a index(a,b) &
> index(b,a) if reads heavily outnumber writes. As index(a) is covered
> by index(a,b), and index(b) by index(b,a), we don't need to add those,
> which saves time on modifi
> In this instance would you create four indexes key(a) key(b) key(a,b) key
> (b,a) ? Or is the decision based on the query response time ?
Depends on the data and usage, but probably I'd go for a index(a,b) &
index(b,a) if reads heavily outnumber writes. As index(a) is covered by
index(a,b), a
In this instance would you create four indexes key(a) key(b) key(a,b) key (b,a)
? Or is the decision based on the query response time ?
On 11 Oct 2011, at 13:40, Rik Wasmus wrote:
>> Next question. If you have the two separate indexes and then do two
>> queries, one for a and one for b. If you
> Next question. If you have the two separate indexes and then do two
> queries, one for a and one for b. If you then get a list of unique id's
> of both, would it be faster to create an intersection yourself rather
> than have the server do the legwork?
If you only have 2 unrelated indexes on a &
On 2011/10/11 02:30 PM, Alex Schaft wrote:
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no
difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and
'b' in the
second one. Looking for anything that matches
On 2011/10/11 02:22 PM, Rik Wasmus wrote:
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan
> Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
They DO.
See it as lookup table which starts with 'a' in the first case, and 'b' in the
second one. Looking for anything that matches 'b' for an index (a,b) requires
a full scan as you don't know 'a', likewise searchin
The difference is based on the relative frequency of queries where a is
important vs. where b is important. Either way, what will happen is that the
index scan will isolate the first item mentioned, then scan the result set
to isolate the second term.
e.g.
SELECT * FROM someTable WERE a = someVal
Just to clarify having key indexes of (a,b) or (b,a) have no difference ?
On 11 Oct 2011, at 09:36, Johan De Meersman wrote:
> - Original Message -
>> From: "Alex Schaft"
>>
>> If you have a table with columns A & B, and might do a where on A or
>> B, or an order by A, B, would single
- Original Message -
> From: "Alex Schaft"
>
> If you have a table with columns A & B, and might do a where on A or
> B, or an order by A, B, would single column indexes on A and B suffice
> or would performance on the order by query be improved by an index on
> A,B?
Depends on usage :-)
Run
explain select * from A,B where A.col1=B.col1;
The explain plan for your query will tell you what indexes are chosen.
If your explain plan says what you do not like, definitely add an index on col1
in B.
Make sure you run OPTIMIZE TABLE on both tables.
The, run explain select * from A,B wh
John Mancuso wrote:
If I have 2 large tables A and B and I need to join them:
select * from A,B where A.col1=B.col1
If A.col1 is an index and B.col1 is not, if I create an index on B.col1
will this index be used? In other words how are indexes used on table
joins?
Thanks
John Mancuso
Li
Dan Salzer <[EMAIL PROTECTED]> wrote on 16/05/2005 14:36:41:
> I have the following table:
>
> > CREATE TABLE `Article_Search` (
> > `ArticleID` int(11) NOT NULL default '0',
> > `Content` text NOT NULL,
> > PRIMARY KEY (`ArticleID`),
> > FULLTEXT KEY `Content` (`Content`)
> > ) ENGINE=MyISAM DE
rmck <[EMAIL PROTECTED]> wrote:
> I understand that I need to update the db's cardinality for this table
>
>
> I need speed
> Should I run CHECK TABLE or ANALYZE TABLE or myismachk -a?? I need the quickest one
> because with 56179085 records this could take a while...
>
myisamchk -a
Did you run an ANALYZE TABLE?
>> Original Message <<
On 2/4/04, 9:33:30 AM, rmck <[EMAIL PROTECTED]> wrote regarding index
question:
> I ran an insert..select from one table to the other ( changed some column
types to int from varchar on new table).
> the inser
- Original Message -
From: "rmck" <[EMAIL PROTECTED]>
> Are my indexes all gone?? If so how do I recover them! Thanks
hehe ;-)
MySQL just doesn't know the cardinality of the indexes yet (the cardinality
number is a *guess*)
Try CHECK TABLE and I think you will see the cardinality num
- Original Message -
From: "rmck" <[EMAIL PROTECTED]>
> Are my indexes all gone?? If so how do I recover them! Thanks
hehe ;-)
MySQL just doesn't know the cardinality of the indexes yet (the cardinality
number is a *guess*)
Try CHECK TABLE and I think you will see the cardinality num
"John Berman" <[EMAIL PROTECTED]> wrote:
>
> Hi. using MYSql 3.28
There is no such version of MySQL :)
>
> I have a surname column with a standard index and this is the column
> were search are performed on, currently the filed only has the one name
> i.e.:
>
> Surname: smith
>
> I want to in
Lists - Jump <[EMAIL PROTECTED]> wrote:
> Ok, don't shoot me for not entirely understanding indexes.
>
> Can you build an index across two different tables w/in the same
> database? I need an index on fields in table a and in table b and I
> want that index to exist in table a. Is it possible? I
Something else to consider here:
MySQL can use indexes to optimize order by as well, but your order by value can't be
indexed since
it is derived from a database lookup/calculation. If you really need to order by
"diferenca"
you may have to live with it, although you may still see "Using fileso
Hi,
You need to index the column that is used in the where clause
try this
ALTER TABLE tempo_resposta ADD INDEX idmaquina (idmaquina);
HTH
Dobromir Velev
- Original Message -
From: "Leonardo Rodrigues Magalhães" <[EMAIL PROTECTED]>
To: "MySQL ML" <[EMAIL PROTECTED]>
Sent: Friday, June 13
At 1:19 -0600 11/6/02, D. Walton wrote:
At 01:05 AM 11/6/2002 -0600, you wrote:
Paul, the point was to have 'value' be part of the primary key for
pure lookup speed (data file would not need be referenced), but
not to have it effect the uniqueness of the 'id'/'date' key pair
so that I could do
At 01:05 AM 11/6/2002 -0600, you wrote:
Paul, the point was to have 'value' be part of the primary key for pure
lookup speed (data file would not need be referenced), but not to have it
effect the uniqueness of the 'id'/'date' key pair so that I could do an
'insert ignore' into the table with a
At 23:31 -0600 11/5/02, D. Walton wrote:
At 10:32 PM 11/5/2002 -0600, you wrote:
At 20:39 -0600 11/5/02, D. Walton wrote:
At 05:18 PM 11/5/2002 -0800, you wrote:
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
I have a table with 3 fields, 'id', 'date', and 'value'. I've created
At 10:32 PM 11/5/2002 -0600, you wrote:
At 20:39 -0600 11/5/02, D. Walton wrote:
At 05:18 PM 11/5/2002 -0800, you wrote:
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
I have a table with 3 fields, 'id', 'date', and 'value'. I've created a
unique index on 'id' and 'date' in orde
At 20:39 -0600 11/5/02, D. Walton wrote:
At 05:18 PM 11/5/2002 -0800, you wrote:
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
I have a table with 3 fields, 'id', 'date', and 'value'. I've created a
unique index on 'id' and 'date' in order to lookup 'value' quickly. I
would li
At 05:18 PM 11/5/2002 -0800, you wrote:
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
>
> I have a table with 3 fields, 'id', 'date', and 'value'. I've created a
> unique index on 'id' and 'date' in order to lookup 'value' quickly. I
> would like to be able to add 'value' to the in
On Tue, Nov 05, 2002 at 07:14:25PM -0600, D. Walton wrote:
>
> I have a table with 3 fields, 'id', 'date', and 'value'. I've created a
> unique index on 'id' and 'date' in order to lookup 'value' quickly. I
> would like to be able to add 'value' to the index so that the data files
> does not
>Hello All,
>
>I've got a question that is likely an easy one, I just want confirmation from
>my peers.
>
>I have tables with a timestamp column and perform many selects and counts from
>these tables based on the date that the record was written.
>
>Can/should I index a timestamp column? I do my b
From: <[EMAIL PROTECTED]>
> Can/should I index a timestamp column?
Can: Yes.
Should: Why not?
> Should I treat it as a char and
> limit it to just index on the portion of the data that distinguishes the date
Hmm, I wouldn't. Offhand, I'd guess that MySQL handles time/date things as integers
In the first query, mysqld could get all the information it
needed from table a from the index file.
In the second query, it needed to read the data file to get all
the columns, and determined that using an index would not gain
any speed. From the 3 reow returned, I woul guess that you
don't hav
Hi,
For such small tables, does it matter.
MySQL will optimise queries the best way it thinks it can. In this case, in
the first query, only the index file for student_info3 will be used, so
that might have a bearing.
In the second query, where all columns from the student table are requested
w
If the order of your columns in the index is what you say ( Vessel + Voyage
+ Port ), MySQL will use the index if you specify the Vessel and Voyage
values.
The index could not be used if you didn't use the first column of the index
(for example, specifying values for Voyage and Port columns)...
P
35 matches
Mail list logo
