The query doesn't use the specified indexes
Hi, I have made an InnoDB table and I am trying to search using some keys, but they are not used, and the query takes a very long time. Here is a test table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `symbol` varchar(20) NOT NULL, `market` varchar(20) NOT NULL, `id_symbol` int(10) unsigned NOT NULL, `id_market` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `symbol` (`symbol`), KEY `market` (`market`), KEY `id_symbol` (`id_symbol`), KEY `id_market` (`id_market`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The search query is: mysql explain select * from test where symbol='etc' order by market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: symbol key: symbol key_len: 62 ref: const rows: 1 Extra: Using where; Using filesort The bad part is Using filesort, and I thought that this is because it doesn't like varchar or char columns for indexes, so I tried to use columns that contain integers: mysql explain select * from test where id_symbol=2 order by id_market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: id_symbol key: id_symbol key_len: 4 ref: const rows: 1 Extra: Using where; Using filesort It still uses Using filesort and it doesn't use the index id_market in the query. So I tried to force using the indexes: mysql explain select * from test force index(symbol, market) where symbol='etc' order by market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: symbol key: symbol key_len: 62 ref: const rows: 1 Extra: Using where; Using filesort So, no matter I do, the query doesn't want to use the specified index. Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug? The current table I am testing has no records. I have also tried this on a table that has more than 10 million records, with exactly the same results. Please tell me what can I do. Thanks. -- Octavian __ Information from ESET NOD32 Antivirus, version of virus signature database 5214 (20100621) __ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
Re: The query doesn't use the specified indexes
Hi Octavian, all! Octavian Rasnita wrote: Hi, I have made an InnoDB table and I am trying to search using some keys, but they are not used, and the query takes a very long time. Here is a test table: CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `symbol` varchar(20) NOT NULL, `market` varchar(20) NOT NULL, `id_symbol` int(10) unsigned NOT NULL, `id_market` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `symbol` (`symbol`), KEY `market` (`market`), KEY `id_symbol` (`id_symbol`), KEY `id_market` (`id_market`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 So you have a table with 5 columns, one being the primary key, and separate single-column indexes on the other 4 columns. The search query is: mysql explain select * from test where symbol='etc' order by market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: symbol key: symbol key_len: 62 ref: const rows: 1 Extra: Using where; Using filesort The bad part is Using filesort, No, it works as designed: What I take from the output is that it will use the index (key) on column symbol (to find all rows that contain the constant value 'etc' in that column), and then it will sort those rows (order by market) to return the first 20. and I thought that this is because it doesn't like varchar or char columns for indexes, so I tried to use columns that contain integers: mysql explain select * from test where id_symbol=2 order by id_market limit 20\G *** 1. row *** id: 1 select_type: SIMPLE table: test type: ref possible_keys: id_symbol key: id_symbol key_len: 4 ref: const rows: 1 Extra: Using where; Using filesort It still uses Using filesort and it doesn't use the index id_market in the query. This query cannot use the index on id_market because using that index would ignore the condition id_symbol=2. So I tried to force using the indexes: mysql explain select * from test force index(symbol, market) where symbol='etc' order by market limit 20\G Unless you changed your table definition, there is no index combining these two fields - you didn't create any. [[...]] So, no matter I do, the query doesn't want to use the specified index. Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug? See above. If you expect the system to use an index on two columns, you should first create it. The current table I am testing has no records. I have also tried this on a table that has more than 10 million records, with exactly the same results. You cannot test execution strategies on empty tables - it doesn't make any sense. The moment the optimizer uses statistical information (cost estimates, aka cost-based optimizer), it will detect that the table is empty, so there is no use in going through an index because that will not reduce the number of rows (to check) any further. If you want to test execution strategies, you should first make sure that your test tables contain data which are roughly realistic, with a distribution of values that is roughly realistic, and that your indexes will provide a decent selectivity (I'd guess, at the very least you need 20 different values per column). It is a separate question whether that sorting is critical: You mention 10 million records, but you don't tell us the distribution of values. If there are 10,000 different values of symbol, on average such a query would have to sort 1000 records, which shouldn't be too bad. Please tell me what can I do. Apart from the hints above: Make your mail client break long lines. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz Amtsgericht Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL crashes
Anybody have any idea why MySQL would start to have this error message every 4 or so days at midnight: Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) This just started happening a little more than 2 weeks ago. The server has been running fine for almost two years. MySQL is on a Linux system. There are about 750 dbs and accessed by approximately half that number websites (375). Half of the websites are on a different server than the MySQL server. Charlene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL crashes
Charlene, You should check the mysql error log and if there isn't a message about the cause there you may want to check /var/log/syslog for mysql being killed by oomkiller. Tyler On 6/21/10 11:11 AM, Charlene wrote: Anybody have any idea why MySQL would start to have this error message every 4 or so days at midnight: Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) This just started happening a little more than 2 weeks ago. The server has been running fine for almost two years. MySQL is on a Linux system. There are about 750 dbs and accessed by approximately half that number websites (375). Half of the websites are on a different server than the MySQL server. Charlene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL crashes
Charlene wrote: Anybody have any idea why MySQL would start to have this error message every 4 or so days at midnight: Do look at the logs, but if the error always occurs at midnight, you might look for a disk backup or compression program that's scheduled to run than and accesses the database files incompatibly. Cheers, Ann -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL crashes
you can stop your Mysql server (the program) and delete the file mysql.sock and the try to start. 2010/6/21 Tyler Poland tpol...@engineyard.com Charlene, You should check the mysql error log and if there isn't a message about the cause there you may want to check /var/log/syslog for mysql being killed by oomkiller. Tyler On 6/21/10 11:11 AM, Charlene wrote: Anybody have any idea why MySQL would start to have this error message every 4 or so days at midnight: Connection error: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) This just started happening a little more than 2 weeks ago. The server has been running fine for almost two years. MySQL is on a Linux system. There are about 750 dbs and accessed by approximately half that number websites (375). Half of the websites are on a different server than the MySQL server. Charlene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=guifre.bo...@gmail.com -- Guifre Bosch Fabregas Tlf.: 687911075
A Marco Zoptta desk lamp
I have a Marco Zotta Arcobaleno desk lamp that requires repair. The Museum of Modern Art in NYC has one in its collectionon. The transformer needs replacing. Can you advise who can do what is required and where can repair be done?. It is shown on Marco Zotta's website. Your advice will be greatly appreciated. Thanks in advance. F. Van Poznak, MD _vanpe...@aol.com_ (mailto:vanpe...@aol.com)
