Does my query look good?

[siliconmike]

I've got a table t with rows a,b,c,d,e,f
and a single index i on a,b,c,d,e,f

The query is:

SELECT * FROM t WHERE a="something" AND b="something" AND c="something"
ORDER BY d, e, f LIMIT 100;

now, table t may have millions of rows.

Is this query efficient ? Any comments?

Mike

[Aggro]

siliconmike wrote:

I've got a table t with rows a,b,c,d,e,f
and a single index i on a,b,c,d,e,f

The query is:

SELECT * FROM t WHERE a="something" AND b="something" AND c="something"
ORDER BY d, e, f LIMIT 100;

now, table t may have millions of rows.

Is this query efficient ? Any comments?

If a,b and c are indexed and their values in combined match only to a
few thousands of rows, then the query should be pretty fast.

If you don't have indexes or they are for some reason not used, the
database will have to check each and every row in database, which might
take a while. Also, if values match to a large amount of rows, the query
will propably slow down.

Explain tells you more:
EXPLAIN SELECT * FROM t WHERE a="something" AND b="something" AND
c="something" ORDER BY d, e, f LIMIT 100;

[Bill Karwin]

siliconmike wrote:

and a single index i on a,b,c,d,e,f

SELECT * FROM t WHERE a="something" AND b="something" AND c="something"
ORDER BY d, e, f LIMIT 100;

You probably need at least one additional index, defined on columns (d,
e, f).

Read this page:
http://dev.mysql.com/doc/mysql/en/mu...n-indexes.html

And also see additional explanation that I wrote in a posting last year:
http://groups-beta.google.com/group/...19b977f48d3f63

Regards,
Bill K.