Indexes

[Shiraz]

Is there some manual on how to make indexes?

for example:
table is
create mytable
(
a varchar,
b varchar,
c int,
d int
);

the data fields are (a,b,c,d) and my select statements can be
1. select a,b,sum(c)-sum(d) from mytable;
2. select a, sum(c) from mytable;
3. select b, sum(d) from mytable;

Which of the following structure makes a better index:
one index:
create index index1 on mytable (a,b);

or

two indexes:
create index index1 on mytable (a);
create index index2 on mytable (b);

[Andy Jeffries]

On Mon, 09 Jan 2006 23:01:01 -0800, Shiraz wrote:

the data fields are (a,b,c,d) and my select statements can be 1. select
a,b,sum(c)-sum(d) from mytable; 2. select a, sum(c) from mytable;
3. select b, sum(d) from mytable;

Which of the following structure makes a better index: one index:
create index index1 on mytable (a,b);

or

two indexes:
create index index1 on mytable (a);
create index index2 on mytable (b);

Have you tried create all three indexes (they can co-exist), then run
"EXPLAIN SELECT a,b,sum(c)-sum(d)" and the other queries on your tables?

This is the way to find your answer, depending on data distribution
sometimes MySQL will use different queries for the same table and query
structure.

Also, as I understood it the indexes were only used when you had a WHERE
clause (as otherwise you're operating on the whole dataset so there's no
point working out which order to iterate through the whole lot on).

Anyway, I may be wrong on that last point - but you should create the
indexes and test which indexes are used (MySQL will *generally* use the
fastest/best index).

Cheers,
Andy

--
Andy Jeffries | gPHPEdit Lead Developer
http://www.gphpedit.org | PHP editor for Gnome 2
http://www.andyjeffries.co.uk | Personal site and photos

[Gordon Burditt]

>Is there some manual on how to make indexes?

I often make them with:

alter table mytable add index (foo);
or
alter table mytable add unique (foo);
or
alter table mytable add primary key (foo);

for example:
table is
create mytable
(
a varchar,
b varchar, You need a length on varchar fields. c int,
d int
);

the data fields are (a,b,c,d) and my select statements can be
1. select a,b,sum(c)-sum(d) from mytable;
2. select a, sum(c) from mytable;
3. select b, sum(d) from mytable;
All of these select the entire table. And they don't use ORDER BY.
Chances are use of an index would not be very productive here.

Usually, the index you want relates to conditions in the WHERE
clause or what you ORDER BY. Also, if you want something to be
unique, you want a UNIQUE index or PRIMARY KEY on it.
Which of the following structure makes a better index:
one index:
create index index1 on mytable (a,b);

or

two indexes:
create index index1 on mytable (a);
create index index2 on mytable (b);

You can't use two separate indexes for the same query. (Depending
on the types of queries you make, it could be a good idea to have
them for different queries).

For example, in a phone book, it's generally ordered by (lastname,
firstname). You can do a lookup if it were ordered by (lastname)
or by (firstname), it's just slower. But a second phone book ordered
by (firstname) is no help if you have one ordered by (lastname)
only.

Gordon L. Burditt

[Shiraz]

I understand what you are saying here. Different queries can use
different indexes, so as defined below i have three queries and there
can be an index defined for all of them specifically that works best
for that query.
alter table mytable add index (a);
alter table mytable add index (b);
alter table mytable add index (a,b);

You can't use two separate indexes for the same query. (Depending
on the types of queries you make, it could be a good idea to have
them for different queries).
Let me add to the problem: for the table defined above (mytable), with
fields a,b,c,d in need 3 basic queries:

select a, sum(c)
where a > '2005'
group by a;

and

select b, sum(d)
where a > '2005'
group by a;

and

select a, b, sum(c) - sum(d)
where a > '2005'
and b > '-1'
group by a,b;
Also, as I understood it the indexes were only used when you had a WHERE
clause (as otherwise you're operating on the whole dataset so there's no
point working out which order to iterate through the whole lot on).
and given that the data set is going to be thousands of rows, does it
make sense to make 2 indexes
alter table mytable add index (a);
alter table mytable add index (b);

or just one;
alter table mytable add index (a,b);

OR can i have all three in there? considering space in not an issue?
For example, in a phone book, it's generally ordered by (lastname,
firstname). You can do a lookup if it were ordered by (lastname)
or by (firstname), it's just slower. But a second phone book ordered
by (firstname) is no help if you have one ordered by (lastname)
only.

[Gordon Burditt]

>I understand what you are saying here. Different queries can use

different indexes, so as defined below i have three queries and there
can be an index defined for all of them specifically that works best
for that query.
alter table mytable add index (a);
alter table mytable add index (b);
alter table mytable add index (a,b);
Having many indexes slows down record insertion and deletion (and updating
the key fields). It can also dramatically speed up selects.
You might not want to add an index appropriate for a query used once
a year at tax time. You might well want to add an index appropriate
for a query used a dozen times a second.

You can't use two separate indexes for the same query. (Depending
on the types of queries you make, it could be a good idea to have
them for different queries).

Let me add to the problem: for the table defined above (mytable), with
fields a,b,c,d in need 3 basic queries:

select a, sum(c)
where a > '2005'
group by a;

This query could make good use of an index on (a) (for the WHERE
and GROUP BY).

and

select b, sum(d)
where a > '2005'
group by a;
This query could make good use of an index on (a) (for the WHERE
and GROUP BY).

and

select a, b, sum(c) - sum(d)
where a > '2005'
and b > '-1'
group by a,b;
This query could make good use of an index on (a) (for the WHERE)
or on (a,b) (for the WHERE and the GROUP BY) or on (b) (for the
WHERE) or on (b,a) (for the WHERE). Which one it will pick if all
are available, I'm not sure. It may well depend on things like whether
a or b have more unique values.

Also, as I understood it the indexes were only used when you had a WHERE
clause (as otherwise you're operating on the whole dataset so there's no
point working out which order to iterate through the whole lot on).
ORDER BY and GROUP BY may also take advantages of indexes if the index
matches what you're ordering by or grouping by.
and given that the data set is going to be thousands of rows, does it
make sense to make 2 indexes
alter table mytable add index (a);
alter table mytable add index (b);

or just one;
alter table mytable add index (a,b);

OR can i have all three in there? considering space in not an issue?

If space is not an issue, speed still might be. (How much are you
updating the data? Or is this 99.9% reads?) Yes, you can have all
three in there. And I believe an index for (a,b) can substitute
for one on (a) in some situations. However, an index for (b,a)
cannot substitute for an index on (a).
For example, in a phone book, it's generally ordered by (lastname,
firstname). You can do a lookup if it were ordered by (lastname)
or by (firstname), it's just slower. But a second phone book ordered
by (firstname) is no help if you have one ordered by (lastname)
only.

Gordon L. Burditt

[Shiraz]

I have about 2 million inserts, 2 million updates and about 10000
selects daily but I understand what you are saying.

Having many indexes slows down record insertion and deletion (and updating
the key fields). It can also dramatically speed up selects.
You might not want to add an index appropriate for a query used once
a year at tax time. You might well want to add an index appropriate
for a query used a dozen times a second.
This answers some of my concerns: its possible to have some benefit
from an index for (a,b) in lieu of an index (a)And I believe an index for (a,b) can substitute
for one on (a) in some situations. However, an index for (b,a)
cannot substitute for an index on (a).

Will try some tests on a data set and post the results. now that i
somewhat undestand what to look for and what are the variables.
Thanks for the help

SK