Multi column index

[Matthias Braun]

Help!

Probably it is too easy, but I am not so experienced in MySQL up to now.

I have two tables table1&table2 with the following columns: col1 double,
col2 int(11), col3 date

I want to query:

INSERT INTO table2 SELECT col1,col2,col3 FROM table1
WHERE col2= ( SELECT MAX(col2)
FROM table1
WHERE table1.col1=tab le2.col1 AND
table1.col3=tab le2.col3
);

It is working fine, but not very fast. Do you know the best solution,
probably a multi-column index (INDEX indexname(col1, col3))? Or do I need
an index also for col2?

Thanks,

Matthias

[Chuck Gadd]

Re: Multi column index

On Sun, 30 Nov 2003 02:14:47 +0100, Matthias Braun <mat_braun@web. de>
wrote:[color=blue]
>It is working fine, but not very fast. Do you know the best solution,
>probably a multi-column index (INDEX indexname(col1, col3))? Or do I need
>an index also for col2?[/color]

Mysql can only use one index per table. So, a multi-column index
would be the right way to go.

If you built your index with Col1,Col3, then any "where" with Col1 and
Col3 can be optimized with that index. And any "where" with just
col1 can be optimized, but a "where" with just col3 would NOT be able
to use the index.



Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Chuck Gadd]

Re: Multi column index

On Sun, 30 Nov 2003 02:14:47 +0100, Matthias Braun <mat_braun@web. de>
wrote:[color=blue]
>It is working fine, but not very fast. Do you know the best solution,
>probably a multi-column index (INDEX indexname(col1, col3))? Or do I need
>an index also for col2?[/color]

Mysql can only use one index per table. So, a multi-column index
would be the right way to go.

If you built your index with Col1,Col3, then any "where" with Col1 and
Col3 can be optimized with that index. And any "where" with just
col1 can be optimized, but a "where" with just col3 would NOT be able
to use the index.



Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Chuck Gadd]

Re: Multi column index

On Sat, 29 Nov 2003 18:21:27 -0700, I wrote:
[color=blue]
>Mysql can only use one index per table.[/color]

Mysql can only use one index per table PER QUERY.


Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Chuck Gadd]

Re: Multi column index

On Sat, 29 Nov 2003 18:21:27 -0700, I wrote:
[color=blue]
>Mysql can only use one index per table.[/color]

Mysql can only use one index per table PER QUERY.


Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Matthias Braun]

Re: Multi column index

Hi Chuck,

I am using MySQL 4.1.0-alpha. Only one index is used for that query,
but the index is a multi-column index, see also

http://www.mysql.com/doc/en/Multiple-column_indexes.html

Matthias


Chuck Gadd schrieb:
[color=blue]
>On Sat, 29 Nov 2003 18:21:27 -0700, I wrote:
>
>
>[color=green]
>>Mysql can only use one index per table.
>>
>>[/color]
>
>Mysql can only use one index per table PER QUERY.
>
>
>Chuck Gadd
>http://www.csd.net/~cgadd/aqua
>
>[/color]

[Matthias Braun]

Re: Multi column index

Hi Chuck,

I am using MySQL 4.1.0-alpha. Only one index is used for that query,
but the index is a multi-column index, see also

http://www.mysql.com/doc/en/Multiple-column_indexes.html

Matthias


Chuck Gadd schrieb:
[color=blue]
>On Sat, 29 Nov 2003 18:21:27 -0700, I wrote:
>
>
>[color=green]
>>Mysql can only use one index per table.
>>
>>[/color]
>
>Mysql can only use one index per table PER QUERY.
>
>
>Chuck Gadd
>http://www.csd.net/~cgadd/aqua
>
>[/color]

[Chuck Gadd]

Re: Multi column index

On Sun, 30 Nov 2003 02:51:07 +0100, Matthias Braun <mat_braun@web. de>
wrote:[color=blue]
>
>I am using MySQL 4.1.0-alpha. Only one index is used for that query,
>but the index is a multi-column index, see also[/color]

I know about multi-colum indexes. I was basically just saying that
you need to use a multi-column index. You could not just create three
seperate single-field indexes and have mysql use them all on the same
query.

But I did mis-read your original posted query.

Lets see, it does:

select ... from table1 where col2=x

So you would need an index on table1.col2 by itself.

And it does

select max(col2) from table1 where table1.col1=tab le2.col1
and table1.col3=tab le2.col3

So you'd need an index on table1.col1+tab le1.col3 and
table2.col1+tab le2.col3

I am GUESSING that mysql would be able to use two different
indexes, since they really are seperate queries. I haven't played
with sub-selects much in mysql, so I'm not sure what the optimizer
will do. And in fact, the optimizer might not do a great job yet.


Chuck Gadd

http://www.csd.net/~cgadd/aqua

[Chuck Gadd]

Re: Multi column index

On Sun, 30 Nov 2003 02:51:07 +0100, Matthias Braun <mat_braun@web. de>
wrote:[color=blue]
>
>I am using MySQL 4.1.0-alpha. Only one index is used for that query,
>but the index is a multi-column index, see also[/color]

I know about multi-colum indexes. I was basically just saying that
you need to use a multi-column index. You could not just create three
seperate single-field indexes and have mysql use them all on the same
query.

But I did mis-read your original posted query.

Lets see, it does:

select ... from table1 where col2=x

So you would need an index on table1.col2 by itself.

And it does

select max(col2) from table1 where table1.col1=tab le2.col1
and table1.col3=tab le2.col3

So you'd need an index on table1.col1+tab le1.col3 and
table2.col1+tab le2.col3

I am GUESSING that mysql would be able to use two different
indexes, since they really are seperate queries. I haven't played
with sub-selects much in mysql, so I'm not sure what the optimizer
will do. And in fact, the optimizer might not do a great job yet.


Chuck Gadd

http://www.csd.net/~cgadd/aqua