Re: adding then removing index produces different query results
mysql mysql wrote:
Thanks for the response Chris, although I can't seem to reproduce the
problem now, but I'm sure you're right.
There's something else strange that I've encountered while trying to
optimize this query. I've got two machines, dev and production. After
adding the index to the title attribute on the dev machine, my query was
reduced from 2 minutes to virtually instantaneous (since the query is sorted
by title). But when executing the query on the production machine, the
query doesn't use the indexed title attribute. Here's what explain says:
Now, why is the production machine performing a filesort and including ALL
the entries? It doesn't seem to be using the indexed title attribute at
all. Why would two different machines with the same indexes and schema
perform two different queries? Could this be happening because I'm running
two slightly different versions of mysql?
Normally queries with like '%xxx%' can't be indexes so maybe the
different version number is playing a part here. Because that string
('xxx') can be anywhere in the text, there's no way for the optimizer to
tell without looking at each row. If you're doing a lot of those sort of
queries maybe look at full text indexes:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
Do you have the same data or at least the same amount of data in your
dev machine? If you don't, you can't compare because the optimizer will
do different things based on the type amount of data.
eg adding 3 rows to a table is useless and you'll never notice a
problem. When you add say 30,000 or 300,000 rows - then you'll really
notice it.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding then removing index produces different query results
On 1/24/08, Chris [EMAIL PROTECTED] wrote:
mysql mysql wrote:
Thanks for the response Chris, although I can't seem to reproduce the
problem now, but I'm sure you're right.
There's something else strange that I've encountered while trying to
optimize this query. I've got two machines, dev and production. After
adding the index to the title attribute on the dev machine, my query was
reduced from 2 minutes to virtually instantaneous (since the query is
sorted
by title). But when executing the query on the production machine, the
query doesn't use the indexed title attribute. Here's what explain
says:
Now, why is the production machine performing a filesort and including
ALL
the entries? It doesn't seem to be using the indexed title attribute at
all. Why would two different machines with the same indexes and schema
perform two different queries? Could this be happening because I'm
running
two slightly different versions of mysql?
Normally queries with like '%xxx%' can't be indexes so maybe the
different version number is playing a part here. Because that string
('xxx') can be anywhere in the text, there's no way for the optimizer to
tell without looking at each row. If you're doing a lot of those sort of
queries maybe look at full text indexes:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
yeah, I've seen that you can't use an index on like with the wildcard at the
front, but I'm trying to use the index when sorting, not on the where
condition..
Do you have the same data or at least the same amount of data in your
dev machine? If you don't, you can't compare because the optimizer will
do different things based on the type amount of data.
eg adding 3 rows to a table is useless and you'll never notice a
problem. When you add say 30,000 or 300,000 rows - then you'll really
notice it.
yeah, almost the exact same dataset. Both around 800,000 rows.
I guess the only way to narrow this down is to upgrade both machines to a
more recent version of mysql and see if the problem persists.. Thanks for
the response
Re: adding then removing index produces different query results
mysql mysql schrieb: Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes query cache, os cache, or some other cache -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: adding then removing index produces different query results
Thanks for the response Chris, although I can't seem to reproduce the problem now, but I'm sure you're right. There's something else strange that I've encountered while trying to optimize this query. I've got two machines, dev and production. After adding the index to the title attribute on the dev machine, my query was reduced from 2 minutes to virtually instantaneous (since the query is sorted by title). But when executing the query on the production machine, the query doesn't use the indexed title attribute. Here's what explain says: DEV MACHINE (mysql 5.1.12-beta-log): mysql show index from phantom_products; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | phantom_products | 0 | PRIMARY |1 | id | A | 759168 | NULL | NULL | | BTREE | | | phantom_products | 1 | label_id_idx |1 | label_id | A | 18075 | NULL | NULL | YES | BTREE | | | phantom_products | 1 | title_idx|1 | title | A | 759168 | NULL | NULL | YES | BTREE | | +--++--+--+-+---+-+--++--++-+ 3 rows in set (0.07 sec) mysql explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%test%' order by title LIMIT 25, 25; ++-+--++---+---+-+++-+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+---+-+++-+ | 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx | 258 | NULL | 759168 | | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+---+-+++-+ 2 rows in set (0.00 sec) PRODUCTION MACHINE (mysql 5.1.22-rc-community) mysql show index from phantom_products; +--++--+--+-+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+-+---+-+--++--++-+ | phantom_products | 0 | PRIMARY |1 | id | A | 781891 | NULL | NULL | | BTREE | | | phantom_products | 1 | label_id_idx |1 | label_id | A | 48868 | NULL | NULL | YES | BTREE | | | phantom_products | 1 | title_idx|1 | title | A | 781891 | NULL | NULL | YES | BTREE | | +--++--+--+-+---+-+--++--++-+ 3 rows in set (0.03 sec) mysql explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%test%' order by title LIMIT 25, 25; ++-+--++---+-+-+---+++ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--++---+-+-+---+++ | 1 | SIMPLE | phantom_products | ALL| label_id_idx | NULL| NULL| NULL | 715038 | Using filesort | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_production.phantom_products.label_id | 1 | Using where |
adding then removing index produces different query results
Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes Here's the SQL I used to produce the behaviour: mysql select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; [data omitted for clarity] 25 rows in set (1 min 50.23 sec) mysql explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--++---+-+-++++ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+-+-++++ | 1 | SIMPLE | phantom_products | ALL| label_id_idx | NULL| NULL| NULL | 787738 | Using filesort | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+-+-++++ 2 rows in set (0.00 sec) mysql create index title_idx on phantom_products(title); Query OK, 777262 rows affected (1 min 58.08 sec) Records: 777262 Duplicates: 0 Warnings: 0 select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; 25 rows in set (11.03 sec) mysql explain select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' order by title LIMIT 75, 25; ++-+--++---+---+-+++-+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--++---+---+-+++-+ | 1 | SIMPLE | phantom_products | index | label_id_idx | title_idx | 258 | NULL | 785367 | | | 1 | SIMPLE | phantom_labels | eq_ref | PRIMARY | PRIMARY | 4 | krad_development.phantom_products.label_id | 1 | Using where | ++-+--++---+---+-+++-+ mysql select phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id where phantom_labels.full_name like '%lame%' LIMIT 75, 25; 25 rows in set (0.01 sec) mysql drop index title_idx on phantom_products; Query OK, 777262 rows affected (53.89 sec) Records: 777262 Duplicates: 0 Warnings: 0 mysql SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; 25 rows in set (0.02 sec) mysql explain SELECT phantom_products.id FROM phantom_products LEFT OUTER JOIN phantom_labels ON phantom_labels.id = phantom_products.label_id WHERE (phantom_labels.full_name like '%lame%') ORDER BY title ASC LIMIT 75, 25; ++-+--+--+---+--+-++---+--+ | id | select_type | table| type | possible_keys | key | key_len | ref| rows | Extra | ++-+--+--+---+--+-++---+--+ | 1 | SIMPLE | phantom_labels | ALL | PRIMARY | NULL | NULL| NULL | 17632 | Using where; Using temporary; Using filesort | | 1 | SIMPLE
Re: adding then removing index produces different query results
mysql mysql wrote: Can anyone explain the following? I encountered the following very strange behaviour while attempting to optimize a query (more details are provided later on for those interested): 1) execute query takes 2 minutes 2) add index 3) execute same query takes 11 seconds 4) drop index 5) execute same query takes 0.2 seconds and uses a different method of returning results from the original query in 1) 6) restart mysql 7) execute query takes 2 minutes Because your o/s does caching as well and probably has the whole .MYD (data) file in memory since there hasn't been a reason to swap it out. Try doing a big query in the middle (select * from other_big_table order by rand() limit 1) and/or restarting mysql between #4 and #5. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
