Re: Order by does not use an index when it should.
On Tue, Jan 07, 2003 at 12:31:36PM +0100, harm wrote: Anybody else who has any idea why the index are not used as they should? I've got same things with 3.23.xx and select query through a TCP/IP connection. I don't know why, but you can solve this issue using the USE INDEX syntax for select queries ... http://www.mysql.com/doc/en/SELECT.html Hmmm, that does help. It does not use the filesort anymore. I cannot use the 'use index' hardcoed in this query (is is not always the fastest). I think the following is happening: A select with only constants and an 'order by' does not use the index for the orderby if there are 2 indexes which differ only in that the last one has the 'order by' column append to it. For example: select * from foo where a=const order by b; indexes: first_index (a), second_index (a,b) The fix should be easy, drop the first_index (It is useless anyway) I'll test this hypothesis tonight when I can do the 20 minute taking alter table :) Confirmed, it does use the index correct since I dropped first_index. Seems like a little bug in the optimiser. Thanks for the help. The load on the server is notecable lower (about 1.00 down !) now the sort uses the index as well! Harmen -- The Moon is Waxing Crescent (49% of Full) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by does not use an index when it should.
On Sat, Jan 04, 2003 at 03:25:00PM +0200, Victoria Reznichenko wrote: On Saturday 04 January 2003 14:53, harm wrote: Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby There is an index on 'nr', it is the primary key :) This is one of the examples from the manual which is supposed to use the index for the order by: SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 Looks like the same situation to me. I tested it on 4.0.7 and MySQL picks up right index. What is your table structure? Anybody else who has any idea why the index are not used as they should? Thanks, Harmen -- The Moon is Waxing Crescent (22% of Full) nieuw.nl - 2dehands.nl: 58470 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by does not use an index when it should.
Anybody else who has any idea why the index are not used as they should? I've got same things with 3.23.xx and select query through a TCP/IP connection. I don't know why, but you can solve this issue using the USE INDEX syntax for select queries ... http://www.mysql.com/doc/en/SELECT.html David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by does not use an index when it should.
On Tue, Jan 07, 2003 at 11:49:05AM +0100, David Bordas wrote: Anybody else who has any idea why the index are not used as they should? I've got same things with 3.23.xx and select query through a TCP/IP connection. I don't know why, but you can solve this issue using the USE INDEX syntax for select queries ... http://www.mysql.com/doc/en/SELECT.html Hmmm, that does help. It does not use the filesort anymore. I cannot use the 'use index' hardcoed in this query (is is not always the fastest). I think the following is happening: A select with only constants and an 'order by' does not use the index for the orderby if there are 2 indexes which differ only in that the last one has the 'order by' column append to it. For example: select * from foo where a=const order by b; indexes: first_index (a), second_index (a,b) The fix should be easy, drop the first_index (It is useless anyway) I'll test this hypothesis tonight when I can do the 20 minute taking alter table :) Thanks, Harmen David - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- The Moon is Waxing Crescent (22% of Full) nieuw.nl - 2dehands.nl: 58501 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by does not use an index when it should.
On Fri, Jan 03, 2003 at 08:24:17PM -0500, Brian Lindner wrote: harm, Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby There is an index on 'nr', it is the primary key :) This is one of the examples from the manual which is supposed to use the index for the order by: SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 Looks like the same situation to me. Thanks, Harmen Hope that helps -- Brian Lindner Friday, January 3, 2003, 8:05:05 PM, you wrote: Hello, I am trying to get a sort to use an index. The query is very simple: select nr from mytable where col1='const' and col2='another const' and col3='YA const' order by nr; The explain gives me: +-+--+--+---+-+---+--+--+ | table | type | possible_keys| key | key_len | ref | rows | Extra| +-+--+--+---+-+---+--+--+ | mytable | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 | const,const,const | 2840 | Using where; Using index; Using filesort | +-+--+--+---+-+---+--+--+ According to the docs (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html) it should use index 'weg_2', which is an index on (col1, col2, col3, nr) but it doesn`t. You are suppost to tell because there is the 'Using filesort' remark (Again according to the docs). And of course the query is quite slow. Index 'weg_5' is (col1, col2, col3). So, to summarise, the select itself is perfectly fine, using indexes as it should, it is just the sort should use the index as wel. Did I misread the docs or is there anything else? Any clues? Thanks for your time, Harmen (Yes I know there are lots of redundant indexes, they are leftovers from tests to figure this out :) (Using 4.0.7, 3.23.50 did the same thing) -- The Moon is Waxing Crescent (3% of Full) nieuw.nl - 2dehands.nl: 57873 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Order by does not use an index when it should.
On Saturday 04 January 2003 14:53, harm wrote: Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby There is an index on 'nr', it is the primary key :) This is one of the examples from the manual which is supposed to use the index for the order by: SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2 Looks like the same situation to me. I tested it on 4.0.7 and MySQL picks up right index. What is your table structure? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Order by does not use an index when it should.
On Sat, Jan 04, 2003 at 03:25:00PM +0200, Victoria Reznichenko wrote:
On Saturday 04 January 2003 14:53, harm wrote:
Your order by will not use your weg_2 index because, as you stated,
weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used
for the individual columns as you need
try to create a separate index on just the nr column, that should be used
by the orderby clause. This way your weg_5 will be used for the where
(the 3 col) and the new index will be for the orderby
There is an index on 'nr', it is the primary key :)
This is one of the examples from the manual which is supposed to use the
index for the order by:
SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2
Looks like the same situation to me.
I tested it on 4.0.7 and MySQL picks up right index.
What is your table structure?
| nr | int(10) unsigned | | PRI | NULL| auto_increment |
| weg| enum('n','y')| | MUL | n ||
| dbase | varchar(15) | | | ||
| land | enum('nl','be') | | | nl ||
There are about 35 other fields so I won`t include them all.
The table has about 1.5 mil rows. Type is innodb.
Query:
describe select nr from ad where dbase='auto_access' and weg=n and land=nl order
by ad.nr;
Describe:
| table | type | possible_keys| key | key_len | ref | rows |
|Extra|
| ad| ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 |
|const,const,const | 4028 | Using where; Using index; Using filesort |
Matches about 1873 rows. (Varies, from 10 to 5000)
Indexes:
| ad| 1 | weg_5 |1 | weg | A
| 20 | NULL | NULL | | BTREE | |
| ad| 1 | weg_5 |2 | land| A
| 20 | NULL | NULL | | BTREE | |
| ad| 1 | weg_5 |3 | dbase | A
| 20 | NULL | NULL | | BTREE | |
| ad| 1 | weg_2 |1 | weg | A
| 20 | NULL | NULL | | BTREE | |
| ad| 1 | weg_2 |2 | land| A
| 20 | NULL | NULL | | BTREE | |
| ad| 1 | weg_2 |3 | dbase | A
| 890 | NULL | NULL | | BTREE | |
| ad| 1 | weg_2 |4 | nr | A
| 1729450 | NULL | NULL | | BTREE | |
It uses 'weg_5', it should use 'weg_2'.
Thanks for the help,
Harmen
--
The Moon is Waxing Crescent (4% of Full)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Order by does not use an index when it should.
Hello, I am trying to get a sort to use an index. The query is very simple: select nr from mytable where col1='const' and col2='another const' and col3='YA const' order by nr; The explain gives me: +-+--+--+---+-+---+--+--+ | table | type | possible_keys| key | key_len | ref | rows | Extra| +-+--+--+---+-+---+--+--+ | mytable | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 | const,const,const | 2840 | Using where; Using index; Using filesort | +-+--+--+---+-+---+--+--+ According to the docs (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html) it should use index 'weg_2', which is an index on (col1, col2, col3, nr) but it doesn`t. You are suppost to tell because there is the 'Using filesort' remark (Again according to the docs). And of course the query is quite slow. Index 'weg_5' is (col1, col2, col3). So, to summarise, the select itself is perfectly fine, using indexes as it should, it is just the sort should use the index as wel. Did I misread the docs or is there anything else? Any clues? Thanks for your time, Harmen (Yes I know there are lots of redundant indexes, they are leftovers from tests to figure this out :) (Using 4.0.7, 3.23.50 did the same thing) -- The Moon is Waxing Crescent (2% of Full) (filter bait: sql, etc) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Order by does not use an index when it should.
harm, Your order by will not use your weg_2 index because, as you stated, weg_2 index is on (col1, col2, col3, nr) as a group so it wont be used for the individual columns as you need try to create a separate index on just the nr column, that should be used by the orderby clause. This way your weg_5 will be used for the where (the 3 col) and the new index will be for the orderby Hope that helps -- Brian Lindner Friday, January 3, 2003, 8:05:05 PM, you wrote: Hello, I am trying to get a sort to use an index. The query is very simple: select nr from mytable where col1='const' and col2='another const' and col3='YA const' order by nr; The explain gives me: +-+--+--+---+-+---+--+--+ | table | type | possible_keys| key | key_len | ref | rows | Extra| +-+--+--+---+-+---+--+--+ | mytable | ref | bla4,weg,weg_3,weg_4,weg_5,weg_2 | weg_5 | 17 | const,const,const | 2840 | Using where; Using index; Using filesort | +-+--+--+---+-+---+--+--+ According to the docs (http://www.mysql.com/doc/en/ORDER_BY_optimisation.html) it should use index 'weg_2', which is an index on (col1, col2, col3, nr) but it doesn`t. You are suppost to tell because there is the 'Using filesort' remark (Again according to the docs). And of course the query is quite slow. Index 'weg_5' is (col1, col2, col3). So, to summarise, the select itself is perfectly fine, using indexes as it should, it is just the sort should use the index as wel. Did I misread the docs or is there anything else? Any clues? Thanks for your time, Harmen (Yes I know there are lots of redundant indexes, they are leftovers from tests to figure this out :) (Using 4.0.7, 3.23.50 did the same thing) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
