Indexing

alirezaok

what is diffrence between these 2 indexing way?

1-
ALTER TABLE user ADD INDEX ( id1 , id2 ) ;

2-
ALTER TABLE user ADD INDEX ( id2 ) ;
ALTER TABLE user ADD INDEX ( id1 ) ;

thanks

MarkR

They are completely different. The first one adds a single index which indexes id1 and id2 (in that order). The second one adds two independent indexes.

When fetching results from a single table query, only one index may be used as the "main index". This is chosen on a basis of the one which matches the largest amount of the query, and some other factors (e.g. if ORDER BY was used).

So if you had an index on id1 and id2, and you said

SELECT cols FROM user WHERE id1=1 AND id2=2

Then it could efficiently find the row(s) by an index range scan.

Whereas, if you had two independent indexes, it would have to pick one of them, do a range scan, and then discard rows where the rest of the WHERE clause didn't apply.

Try it on some test tables and use EXPLAIN.

Try inserting 1,000,000 rows of random data and time it with different queries and combinations of indexes (on an otherwise idle machine, disabling query cache of course).

Mark

alirezaok

thanks
if i used of:
ALTER TABLE user ADD INDEX ( id2 ) ;
ALTER TABLE user ADD INDEX ( id1 ) ;

and my qery is:
SELECT cols FROM user WHERE id1=1 AND id2=2

do this indexing doesn't have any effect to my script?
or this indexing has effect to my script but is faster than without indexing and slower than ALTER TABLE user ADD INDEX ( id1 , id2 ) ; this idea is right?

thanks

MarkR

alirezaok wrote:
thanks
if i used of:
ALTER TABLE user ADD INDEX ( id2 ) ;
ALTER TABLE user ADD INDEX ( id1 ) ;

and my qery is:
SELECT cols FROM user WHERE id1=1 AND id2=2

do this indexing doesn't have any effect to my script?

Yes, probably. It would use one of those indexes if there wasn't a better one.

In order to determine the precise effect on your application, YOU should benchmark YOUR application, not anybody else's.

Try it on your test server, with a large number of rows in the table and compare with / without the indexes.

Remember also, that indexes are not ALWAYS good - an index takes up more space and makes inserts, updates etc SLOWER - it only speeds up queries which use it, having no effect on others.

or this indexing has effect to my script but is faster than without indexing and slower than ALTER TABLE user ADD INDEX ( id1 , id2 ) ; this idea is right?

To determine what's faster, you MUST benchmark it. See above. Insert a large number of rows in the table on your dev server to see.

Mark