Re: index problem
The optimizer is right, you are wrong, as simple as that :-) value between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to start = 1988778880 and end = 1988778880 and the optimizer should pick up the index. Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-) - Original Message - From: xucheng xuch...@sankuai.com To: mysql@lists.mysql.com Sent: Thursday, 28 July, 2011 2:50:46 PM Subject: index problem Hi i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: index problem
[Note to self, reply to mailinglist, not to author ;) ] i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) The query optimizer examined your answer, and decided a full-table scan was faster then using an index. It estimated it would require less IO operations to read the table in sequence in this case then reading the index fetching the appropriate records from the table. Turned out if was right. This is often the case when large portions of a table (or index) could possibly matched by the first guess. Here, the first 'guess' is that everything below start = 1988778880 is a possible match (as it's first field of the index `range`). Every one of them has to be verified of having an `end` your number, and has to fetch the appropriate record if it does which is costly in harddisk IO. BTW: as this looks as a GeoIP query, based on IP, if the `start` `end` ranges cannot overlap, this is probably faster: SELECT * FROM geo_query WHERE 1988778880 start ORDER BY start DESC LIMIT 1. -- Rik Wasmus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: index problem
thanks . i dropped the primary key , and it still didn't use the index . when i dropped the index `range`, and add two indexes `start` and `end` . it picks up the index , but it still used more seconds than using no index with `start` and `end` indexed as one --`range`. 2011/7/28 Johan De Meersman vegiv...@tuxera.be: The optimizer is right, you are wrong, as simple as that :-) value between [field1] and [field2] cannot use indices, as your primary reference is a constant, not a field. Rewrite that to start = 1988778880 and end = 1988778880 and the optimizer should pick up the index. Index hints are rarely ever needed. It's best to stay away from them unless you know exactly what's going on under the hood :-) - Original Message - From: xucheng xuch...@sankuai.com To: mysql@lists.mysql.com Sent: Thursday, 28 July, 2011 2:50:46 PM Subject: index problem Hi i found a strange problem . when i using index for 'select' , i got a slower result than without index . i have a tabe : create table geo_query ( `id` int(10) unsigned not null auto_increment , `start` bigint(20) unsigned not null , `end` bigint(20) unsigned not null, `desc` varchar(1000) not null, primary key (`id`) , key `range` (`start`,`end`) ) engine=myisam ; the whole table contains 43 rows . 1, the query ' select * from geo_query where 1988778880 between start and end ;' used 0.15 second ; and i used 'explain' and found that it didn't use index and scanned the whole table . 2, so i changed the query for ' select * from geo_query force index(`range`) where 1988778880 between start and end ;' . it used 0.36 second . i can't figure it out .why the query used index spend more time than not ? any comment appreciate : ) -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=helloworldje...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index problem ?
Michael Gale wrote: When I run the following: `Select DISTINCT machine from syslog WHERE date1 (NOW() - INTERVAL 1 hour);' it takes 9min to complete. If I use Explain it says the query is using index hostname, should it not be using the index hostdate which contains fields machine and date1 since those are the fields I am using in my query ? Your WHERE clause needs an index on date1, which means an index that *starts* with date1. Your index on (machine, date1) won't help in this case. You could use an index on just date1 or on (date1, machine). The second would allow MySQL to do that query from the index alone, without referring to the data file, so it should be much faster than what you're doing now. -- Keith Ivey [EMAIL PROTECTED] Smokefree DC http://www.smokefreedc.org Washington, DC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index problem
Oropeza Querejeta, Alejandro [EMAIL PROTECTED] wrote: Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished). does anyone have any idea? Wait until it finishes.:) We have tables where index creation takes tens of hours or even sometimes _days_, not even hours. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index problem
What's the definition of the table? IE are you indexing an INT, VARCHAR, etc? What's the definition of the index? Is it unique, composite, etc? What's the storage engine in use? InnoDB? MyISAM? Can you show the relevant parts of your my.cnf file? What operating system are you using? David Oropeza Querejeta, Alejandro wrote: Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished). does anyone have any idea? the server is 2Xeon 2.8 gigs with 6 Gb of ram. Best regards Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index problem
Below are the answers Best Regards -Mensaje original- De: David Griffiths [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 30 de Junio de 2004 01:29 p.m. Para: [EMAIL PROTECTED] Asunto: Re: Index problem What's the definition of the table? IE are you indexing an INT, VARCHAR, etc? 3 fields Folio, Vacante, int Folio2 char(10) What's the definition of the index? Is it unique, composite, etc? Nonunique, single column (folio2) What's the storage engine in use? InnoDB? MyISAM? Myisam Can you show the relevant parts of your my.cnf file? I have the standard My-huge.cnf What operating system are you using? Redhat Linux 7.3 David Oropeza Querejeta, Alejandro wrote: Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished). does anyone have any idea? the server is 2Xeon 2.8 gigs with 6 Gb of ram. Best regards Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index problem
So the table is, folio int vacante int folio2 char(10) and the table type is MyISAM create index some_index on table(folio2); and the table has about 200,000,000 rows. MyISAM creates a file per table for table data, and for index data. You can find the files created underneath the mysql install directory in a directory with the database name (mysql/var if you are using source-compiled and mysql/data if you are using pre-compiled binaries). To quote the docs, Each |MyISAM| table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm' file stores the table definition. The data file has an `.MYD' (MYData) extension. The index file has an `.MYI' (MYIndex) extension, What's the max file size on your system? I suspect it's greater than 2 gigabytes if you have 200 million rows. But something to check. You might be exceeding the capabilities of the MyISAM storage engine, or the version of MySQL you are using (which version *are* you using? 3.23 or a 4.0.x, or 4.1?). Can you reduce the size of the index by creating a partial index, like create index some_index on table(folio2(5)); to only index part of the data? David Oropeza Querejeta, Alejandro wrote Below are the answers Best Regards -Mensaje original- De: David Griffiths [mailto:[EMAIL PROTECTED] Enviado el: Miércoles, 30 de Junio de 2004 01:29 p.m. Para: [EMAIL PROTECTED] Asunto: Re: Index problem What's the definition of the table? IE are you indexing an INT, VARCHAR, etc? 3 fields Folio, Vacante, int Folio2 char(10) What's the definition of the index? Is it unique, composite, etc? Nonunique, single column (folio2) What's the storage engine in use? InnoDB? MyISAM? Myisam Can you show the relevant parts of your my.cnf file? I have the standard My-huge.cnf What operating system are you using? Redhat Linux 7.3 David Oropeza Querejeta, Alejandro wrote: Hi, i'm trying to create an index on a table with 199 million records. The problem is that is taking too long (8 hours and is not yet finnished). does anyone have any idea? the server is 2Xeon 2.8 gigs with 6 Gb of ram. Best regards Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: index problem
In the first statement you are selecting * (ALL the row) from tbl1 with no restricting comment (e.g. where) so if you are going to select every thing in the table you do not need to use the index (MySQL uses B-tree). Simon -Original Message- From: Prasanth Krishna [mailto:[EMAIL PROTECTED]] Sent: 16 January 2003 09:31 To: [EMAIL PROTECTED] Subject: index problem hi i have a database with a single table say tbl1 with an index on a particular field say col1. when i say select * from tbl1; it doesn't use the index on that table. but if i say select col1 from tbl1; it uses the index. how to make mysql use the index on col1 for the first query? thanks in advance. Prasanth - 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 - 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: index problem
At 2:31 AM -0700 1/16/03, Prasanth Krishna wrote: hi i have a database with a single table say tbl1 with an index on a particular field say col1. when i say select * from tbl1; it doesn't use the index on that table. In this query, I can't see any reason to use an index...MySQL is simply returning all columns from all records in whatever order it sees fit. If you used select * from tbl1 order by col1 it *would* use the index. but if i say select col1 from tbl1; it uses the index. I'm guessing that in this case MySQL is reading col1 directly from the index file...it doesn't need to look at the actual data record at all. Which is good. how to make mysql use the index on col1 for the first query? Give it a reason to do so ;) -steve -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | SETI@Home: 1001 Work units on 23 oct 2002 | | 3.152 years CPU time, 3.142 years SETI user... and STILL no aliens... | ++ - 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: index problem
I was try with explain or describe command. The result in column 'type' show 'ALL' , column ' possible keys' show index name I was created. I wonder that why did not use index it take a long time (look like computer hang). SF At 14:23 22/1/2002 +0700, Kittiphum Worachat wrote: Hi Try to check your query by this command explain select * from holder, management where holder.id=management.id or (holder.name=management.name and holder.surname=management.surname) order by holder.no and let take a look the result if they say the result come from ALL record it mean you can't get the benifit of index Kittiphum Worachat,MT. www.hatyailab.com Hi, I have problem with these query: select * from holder, management where holder.id=management.id or (holder.name=management.name and holder.surname=management.surname) order by holder.no It take a long time (more than 1 minute) with thousand record. I have index within 2 table (name+surname, id). It did not have problem if I check only id or name+surname. SF - 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 - 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 - 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: index problem
Hi Try to check your query by this command explain select * from holder, management where holder.id=management.id or (holder.name=management.name and holder.surname=management.surname) order by holder.no and let take a look the result if they say the result come from ALL record it mean you can't get the benifit of index Kittiphum Worachat,MT. www.hatyailab.com Hi, I have problem with these query: select * from holder, management where holder.id=management.id or (holder.name=management.name and holder.surname=management.surname) order by holder.no It take a long time (more than 1 minute) with thousand record. I have index within 2 table (name+surname, id). It did not have problem if I check only id or name+surname. SF - 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 - 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: Index problem (I think) with MySQL 3.23.36 on Solaris -- hang
Responding to my own email... sheesh. It's just that kinda day. : I found reference in the archives to what seems to be the same problem. Monty replied to a thread entitled I got it hanged ! on 2001-07-17, and agreed that it shouldn't be happening. Unfortunately, I can't find a solution in the archives. I've had to shutdown mysqld. mysqladmin -p shutdown prevents the client from connecting, but the process still shows up with ps -ef | grep mysql. I eventually had to issue a kill -9 finally to remove it. Think I'm gonna install 4.0, but I'm still interested in a solution to this problem. Thanks all, /Rob At 15:05 -0500 2001/10/30, Robert Alexander wrote: Hi all, mysqld seems to 'hang' under certain circumstances when I'm doing a fairly large ALTER TABLE -- adding or dropping an Index, repair etc. The server and client are still 'alive' and I can do simple things like show processlist, or selecting a count(0) from another table, but other selects or updates of other tables just sit there forever in the process list. mysqld's server usage goes up to over 99%, and the files for the table being ALTERed (in the data directory) are no longer being updated -- the size, last modified time, etc, do not change. It seems that if I issue just the ALTER TABLE statement, and then don't try to do ANYTHING else, it'll complete successfully (eventually), but if I issue ANY other select/update statement on another table, the CPU goes up to 99% and everything just stops. I'm running MySQL v.3.23.36 binary on an UltraSparc 1 with Solaris 8 (patched to 2001/04 IIRC). Any hints, ideas, suggestions? Any additional info I can provide? Thanks much, everyone. I'll summarize. Best, /Rob - 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
