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
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
Change storage location
Hi All, I have newly installed mysql 3.23.54 in Red Hat 9 (The one which was bundled with the OS). And default the data storage file location went to /var/lib/mysql, how do I change the location to /mysql, I have also tried modifying my.cnf it didn't help. Any suggestion. -mysql - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
How to execute a file in mysql prompt
Hi, I have a file which has Creation of tables and views. How can I run the file in mysql prompt. -mysql __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any one can drop a table or database
Hi, I am new to mysql. I have installed, created a database and also some created tables. Now any one who can login to the host are able to drop the table or also the database. How do I protect it? -mysql - Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing
Post-install
Just installed mssql and in the end of the installation I receive the message taht now is the time to change root password for the db using mysqladmin using the following synthax: /usr/bin/mysqladmin -u root -p password 'new password' So issued the following command: /usr/bin/mysqladmin -u root -p qazwsx 22 (qazwsx is my current root password in OS, 22 is desired password for sql ) Then I see the prompt: Enter password:(which password?) I enter 22 (I tried qazwsx either) I receive the error message: error: Access denied for user 'root@localhost' (Using password: YES)' - 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
