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 : ) -- 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
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
Index problem ?
Hello, Currently I am using sysklogd-sql to store syslog messages in a MySQL 4.0.24. The current number of rows is around 3,799,700. The table layout is as follows: ID machine facility priority date1 message Now ID is the primary key and it had one index called hostname containing (machine). I do a lot of queries based off information on the machine name and date1 field. So I created the following index: `CREATE INDEX hostdate ON syslog (machine,date1);` This index has a Cardinality of 1,899,353 - this number is about half of the total number of entries. 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 ? Thanks. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Index problem
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
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]
index problem
Hi, We upgrade one of our servers, but physical and mysql from 3.23.55-max-log to 3.23.58-max-log and run into some problems with a few queries. This is the old server: mysql explain select count(id) from table where entered date_sub(now(), INTERVAL 15 DAY) and DAYOFYEAR(entered) = DAYOFYEAR(date_sub(now(), INTERVAL 14 DAY)) and status in (1, 2); ++---++---+-+--+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | ++---++---+-+--+---++ | table | range | IDX_orders_2,IDX_orders_12 | IDX_orders_12 | 4 | NULL | 49681 | where used | ++---++---+-+--+---++ 1 row in set (0.02 sec) What we did in the move was copy the *.frm and *.MYD and head -c 2048 on every *.MYI and then myisamchk -qr table On the new server the _exakt same_ explain query gives me: ++--+---+--+-+--+-++ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-++ | orders | ALL | IDX_orders_12 | NULL |NULL | NULL | 1841825 | where used | ++--+---+--+-+--+-++ 1 row in set (0.00 sec) Looking for explanations. br Robin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
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
index problem
i am new to mysql. i have got a table tab1 in my database. i am using 4.0.3 version of mysql and the table type is MyISAM. the tab1 has got 14 fields and about 600,000 records and is indexed on col1 which is of varchar type i have used the query select * from tab1 order by col1; which does not use the index. when i used explain select * from tab1 where col1 'const'; -- (const is a valid constant for col1) it gave col1indx in 'key' column but in the 'extra' column i did not get 'using index' instead i got 'using where' only. does it mean that it is not using index? the output of this query is less than 1% of the entire table. how can i make the queries above to use the index? i have used the use index and force index but found out that they are not useful. 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
Re: FIND_IN_SET index problem
Hi.
On Thu 2002-07-25 at 10:49:47 -0500, [EMAIL PROTECTED] wrote:
Hi, I have a problem where the index for a SET column does not get used
once I link in another table.
The output to explain:
mysql explain select count(*) from NS_articles where
find_in_set('approved', artFlags) 0;
+-+---+---+--+-+--+--+-+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-+---+---+--+-+--+--+-+
| NS_articles | index | NULL | artFlags | 2 | NULL | 560
| where used; Using index |
+-+---+---+--+-+--+--+-+
This is good, the index on artFlags is being used. Now I link in another
table:
No, the index is not really used (possible_keys = NULL), because you
have an expression (FIND_IN_SET()) instead of a column and MySQL
cannot indexes on (most/any?) expressions. I.e. it is not used to find
the rows based on an index lookup, but MySQL sees that you are only
interested in column(s) from an index and therefore reads in the
index, instead of the data file (using index). Well, in hindsight,
using index surely was not best choice to describe that behaviour.
To make that more clear. Imagine you have 1.000.000 rows, 100 bytes
each. An index on the column of interest needs 10 bytes per row.
Looking up 200 rows of a range without index, reads the whole table
(i.e. the data file): about 1.000.000 * 100 bytes = ~100MB.
Reading them using only the index file (using index) reads 1/10th of
that (10 bytes instead of 100 bytes per row), i.e. 10MB and one can
assume that this will also be faster about 10 times (this is specific
to this example, of course). That is why MySQL does this in your case.
Really making use of the index for the lookup, needs about 1 index
lookup for the first row, reading log2(1.000.000) =~ 20 index pages
(each 1KB usually) plus the 199 remaining rows, each 100 bytes, or 19
index pages (10 fitting in one 1KB page; in reality, it's less, let's
say 5). I did not consider the data file intentionally (using index
additionally, you know). So it reads less than 60KB, but needs
additional 20 disk seeks. That would be real good! ;-)
mysql explain select count(*) searchTotal from NS_articles nsa,
NS_editors nse where find_in_set('approved', nsa.artFlags) 0 and
nsa.artEditor = nse.id;
+---+---+---+---+-++--+-+
| table | type | possible_keys | key | key_len | ref| rows |
Extra |
+---+---+---+---+-++--+-+
| nse | index | PRIMARY | PRIMARY | 32 | NULL | 21 |
Using index |
| nsa | ref | artEditor | artEditor | 32 | nse.id | 19 |
where used |
+---+---+---+---+-++--+-+
What happened to the index on artFlags? The table link uses indexes
which is good, but now artFlags is doing a raw table scan.
I presume it is because you do not have any key in NS_articles which
covers both, id and artFlags. I.e. for using index you would need a
combined key (id, artFlags).
But as explained above, you should get the real speed boost if you can
somehow manage to avoid the expression instead of only the column
name. Unfortuneately, that is not possible in the common case. If you
query very often based on this 'approved' flag, you may want to
consider to give it an own column (which should use indexes
appropriately).
For more details, see the manual sections about index usage and
optimization.
Greetings,
Benjamin.
--
[EMAIL PROTECTED]
-
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
FIND_IN_SET index problem
Hi, I have a problem where the index for a SET column does not get used
once I link in another table.
The output to explain:
mysql explain select count(*) from NS_articles where
find_in_set('approved', artFlags) 0;
+-+---+---+--+-+--+--+-+
| table | type | possible_keys | key | key_len | ref | rows
| Extra |
+-+---+---+--+-+--+--+-+
| NS_articles | index | NULL | artFlags | 2 | NULL | 560
| where used; Using index |
+-+---+---+--+-+--+--+-+
This is good, the index on artFlags is being used. Now I link in another
table:
mysql explain select count(*) searchTotal from NS_articles nsa,
NS_editors nse where find_in_set('approved', nsa.artFlags) 0 and
nsa.artEditor = nse.id;
+---+---+---+---+-++--+-+
| table | type | possible_keys | key | key_len | ref| rows |
Extra |
+---+---+---+---+-++--+-+
| nse | index | PRIMARY | PRIMARY | 32 | NULL | 21 |
Using index |
| nsa | ref | artEditor | artEditor | 32 | nse.id | 19 |
where used |
+---+---+---+---+-++--+-+
What happened to the index on artFlags? The table link uses indexes
which is good, but now artFlags is doing a raw table scan.
TIA
Monte
-
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
Index problem
Hello everybody. I am working with mysql about one week by now, and a friend of mine did an ALTER TABLE, and during this he did a cold reboot. After that, I realize that my tables where somewhat inconsistent. Then I run myisamchk -r, and it told me that my index definition is not valid, and that I must recreate it before trying to repair anything. The problem is, I don't know how to recreate my index definition. When I received this message, I logged on and did a SHOW INDEX, and it did returned a index defition, so what's the problem ? Thanks everybody. - 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
Possible Index problem?
I am using mysql verison 3.23.41-nt I am working with the following two tables. mysql show create table table1; | Table | Create Table - | table1 | CREATE TABLE `table1` ( `Column1` varchar(63) NOT NULL default '', `Column2` varchar(31) default NULL PRIMARY KEY (`Column1`) ) TYPE=MyISAM | mysql show create table table2; | Table | Create Table -- | table2 | CREATE TABLE `table2` ( `Column1` varchar(63) default NULL KEY `idx1` (`Column1`) ) TYPE=MyISAM | ++ 1) When i run the first query through explain the expected results happen mysql explain SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column2; +-+---+---+-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+---+---+-+ | table1 | index | PRIMARY | PRIMARY | 63 | NULL | 57379 | Using index | | table2 | ref | idx1 | idx1| 64 | table1.column1| 1 | Using index | +-+---+---+-+-+---+---+-+ However, when i select an additional column, the following unexpected results happen mysql explain SELECT table1.column1, table1.column2 FROM table1, table2 WHERE table1.column1 = table2.column2 +-+--+---+--+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+---+-+ | table1 | ALL | PRIMARY | NULL |NULL | NULL | 57379 | | | table2 | ref | idx1 | idx1 | 64 | table1.column1| 1 | Using index | +-+--+---+--+-+---+---+-+ 2) Also, look at the following scanario mysql explain SELECT table1.column1 FROM table1, table2 WHERE table1.column1 = table2.column2 order by table1.column1 asc; +-+---+---+-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+---+---+-+ | table1 | index | PRIMARY | PRIMARY | 63 | NULL | 57379 | Using index | | table2 | ref | idx1 | idx1| 64 | table1.column1| 1 | Using index | +-+---+---+-+-+---+---+-+ However, when i select an additional column, the following unexpected results happen mysql explain SELECT table1.column1, table1.column2 FROM table1, table2 WHERE table1.column1 = table2.column2 order by table1.column1 asc +-+--+---+--+-+---+---++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+---++ | table1 | ALL | PRIMARY | NULL |NULL | NULL | 57379 | Using filesort | | table2 | ref | idx1 | idx1 | 64 | table1.column1| 1 | Using index| +-+--+---+--+-+---+---++ I am baffled as to why simple selecting an extra column results in the index no longer being used even though the where clause has not changed. If anybody could provide me an explain as to why this is occuring with a solution, it would be greatly appreciated. TIA __ Do You Yahoo!? Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.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: 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
index problem
Sommai, Tuesday, January 22, 2002, 7:17:36 AM, you wrote: SF I have problem with these query: SF select * from holder, management where holder.id=management.id or SF (holder.name=management.name and holder.surname=management.surname) order SF by holder.no SF It take a long time (more than 1 minute) with thousand record. Show us your table structure, it can help us to solve your problem. SF SF -- For technical support contracts, goto https://order.mysql.com/ 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
index problem
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
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
Index problem (I think) with MySQL 3.23.36 on Solaris
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 ~ Robert Alexander~~ Programmer/Analyst/DBA/Admin WWW Database Applications~~http://www.ra1.net Web Software and Hosting ~~ http://www.workmate.ca - 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
RE: FW: pls help for index problem
hey, Well, how many records are in the table? How many would be returned by the query you present? My prefered (gw) table have 8313193 records for trail (on production should have 70,000,000 records approximately ) . And no. of records are returned have 71430 records by the query. Is the SQL you show the FULL sql? Yes, my full sql statement is that select * from gw where timerecord = '010902' . (Remarks, I want to find out data within a period for analyzing.) What is the output of the EXPLAIN SELECT Shown as below : table type possible_keys key key_len ref rows Extra gw ALL timerecord 8313193 where used What about SHOW INDEX FROM TABLE gw. mysql SHOW INDEX FROM gw; +---+--++---+--- ---+-+---+-+--+- --+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---+--++---+--- ---+-+---+-+--+- --+ | gw| 1 | timerecord |1 | timerecord | A | 119 | NULL | NULL | | | gw| 1 | esnindex|1 | esn | A | 113879 | NULL | NULL | | | gw| 1 | esnindex|2 | timerecord | A | 8313193 | NULL | NULL | | +---+--+---++--- ---+-+---+-+--+- --+ 3 rows in set (0.00 sec) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Thursday, October 25, 2001 6:09 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: FW: pls help for index problem Well, how many records are in the table? How many would be returned by the query you present? Is the SQL you show the FULL sql? What is the output of the EXPLAIN SELECT What about SHOW INDEX FROM TABLE gw. b. kmlau wrote: -Original Message- From: kmlau [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:42 AM To: 'Bill Adams' Subject: RE: pls help for index problem Thanks yr promptly reply !! It seems no any change(improvement) by running explain again after erase quotes. I also ran the command 'myisamchk -a gw.MYI' before sending this consulting mail !! Would U give me more advice ? regards, kmlau -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Tuesday, October 23, 2001 11:11 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pls help for index problem kmlau wrote: I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! U do not need to specify the timestamp as a string, e.g. remove the quotes: timerecord=10902. But more importantly run 'myisamchk -a' on the index (.MYI) file. Doing both of these will help. b. - 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 -- Bill Adams TriQuint Semiconductor - 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: FW: pls help for index problem
Well, how many records are in the table? How many would be returned by the query you present? Is the SQL you show the FULL sql? What is the output of the EXPLAIN SELECT What about SHOW INDEX FROM TABLE gw. b. kmlau wrote: -Original Message- From: kmlau [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:42 AM To: 'Bill Adams' Subject: RE: pls help for index problem Thanks yr promptly reply !! It seems no any change(improvement) by running explain again after erase quotes. I also ran the command 'myisamchk -a gw.MYI' before sending this consulting mail !! Would U give me more advice ? regards, kmlau -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Tuesday, October 23, 2001 11:11 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pls help for index problem kmlau wrote: I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! U do not need to specify the timestamp as a string, e.g. remove the quotes: timerecord=10902. But more importantly run 'myisamchk -a' on the index (.MYI) file. Doing both of these will help. b. - 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 -- Bill Adams TriQuint Semiconductor - 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
pls help for index problem
hi, I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! CREATE TABLE gw( sitename char(12), ip char(15), mac char(17), esn char(12), flag char(2), timerecord timestamp(10), KEY timerecord (timerecord) ); explain select * from gw where timerecord = '010902' table type possible_keyskey key_len ref rows Extra gw ALL timerecord 8313193 where used *remark: The total records in the table gw are 8313193 ! regards, kmlau (I have already read the official document from mysql about index.) - 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: pls help for index problem
kmlau wrote: I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! U do not need to specify the timestamp as a string, e.g. remove the quotes: timerecord=10902. But more importantly run 'myisamchk -a' on the index (.MYI) file. Doing both of these will help. b. - 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
FW: pls help for index problem
-Original Message- From: kmlau [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 24, 2001 9:42 AM To: 'Bill Adams' Subject: RE: pls help for index problem Thanks yr promptly reply !! It seems no any change(improvement) by running explain again after erase quotes. I also ran the command 'myisamchk -a gw.MYI' before sending this consulting mail !! Would U give me more advice ? regards, kmlau -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bill Adams Sent: Tuesday, October 23, 2001 11:11 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: pls help for index problem kmlau wrote: I encountered a problem about indexing. I want to add index on timerecord field in table gw (shown as below) to speed up query relating with time. However, I use explain command (explain select * from gw where timerecord = '010902') to analyze the performace. As a result, it seems the query do not use this index. Would U tell me why and how to correct this !! U do not need to specify the timestamp as a string, e.g. remove the quotes: timerecord=10902. But more importantly run 'myisamchk -a' on the index (.MYI) file. Doing both of these will help. b. - 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
Another Index problem
Thanks for the advice last time folks, I think that one's working. Here's a tougher query, any ideas on how to speed this one up: SELECT DISTINCT badge.last_name AS last_name, badge.first_name AS first_name, badge.bid AS bid, badge.employee AS employee, badge_type.description AS type, badgests.cond_desc AS status, badge.pin AS pin, department.description AS department, facility.description AS facility, badge.expired_date AS expired_date, badge.expired_time AS expired_time, reader.description AS reader, area.description AS area, category.description AS category FROM badge, reader, area, category, badge_type, department, facility, badgests LEFT JOIN smccm_user_vs_permitted_department ON badge.dept = smccm_user_vs_permitted_department.permitted_department WHERE smccm_user_vs_permitted_department.smccm_user = 1 AND smccm_user_vs_permitted_department.permitted_department = department.id AND badge.type = badge_type.id AND badge.dept = department.id AND badge.facility = facility.id AND badge.status = badgests.id AND reader.area = area.id AND (badge.category1 = area.category1 OR badge.category1 = area.category2 OR badge.category1 = area.category3 OR badge.category1 = area.category4 OR badge.category1 = area.category5 OR badge.category1 = area.category6 OR badge.category1 = area.category7 OR badge.category1 = area.category8 OR badge.category1 = area.category9 OR badge.category1 = area.category10 OR badge.category2 = area.category1 OR badge.category2 = area.category2 OR badge.category2 = area.category3 OR badge.category2 = area.category4 OR badge.category2 = area.category5 OR badge.category2 = area.category6 OR badge.category2 = area.category7 OR badge.category2 = area.category8 OR badge.category2 = area.category9 OR badge.category2 = area.category10 OR badge.category3 = area.category1 OR badge.category3 = area.category2 OR badge.category3 = area.category3 OR badge.category3 = area.category4 OR badge.category3 = area.category5 OR badge.category3 = area.category6 OR badge.category3 = area.category7 OR badge.category3 = area.category8 OR badge.category3 = area.category9 OR badge.category3 = area.category10 OR badge.category4 = area.category1 OR badge.category4 = area.category2 OR badge.category4 = area.category3 OR badge.category4 = area.category4 OR badge.category4 = area.category5 OR badge.category4 = area.category6 OR badge.category4 = area.category7 OR badge.category4 = area.category8 OR badge.category4 = area.category9 OR badge.category4 = area.category10 OR badge.category5 = area.category1 OR badge.category5 = area.category2 OR badge.category5 = area.category3 OR badge.category5 = area.category4 OR badge.category5 = area.category5 OR badge.category5 = area.category6 OR badge.category5 = area.category7 OR badge.category5 = area.category8 OR badge.category5 = area.category9 OR badge.category5 = area.category10 OR badge.category6 = area.category1 OR badge.category6 = area.category2 OR badge.category6 = area.category3 OR badge.category6 = area.category4 OR badge.category6 = area.category5 OR badge.category6 = area.category6 OR badge.category6 = area.category7 OR badge.category6 = area.category8 OR badge.category6 = area.category9 OR badge.category6 = area.category10 OR badge.category7 = area.category1 OR badge.category7 = area.category2 OR badge.category7 = area.category3 OR badge.category7 = area.category4 OR badge.category7 = area.category5 OR badge.category7 = area.category6 OR badge.category7 = area.category7 OR badge.category7 = area.category8 OR badge.category7 = area.category9 OR badge.category7 = area.category10 OR badge.category8 = area.category1 OR badge.category8 = area.category2 OR badge.category8 = area.category3 OR badge.category8 = area.category4 OR badge.category8 = area.category5 OR badge.category8 = area.category6 OR badge.category8 = area.category7 OR badge.category8 = area.category8 OR badge.category8 = area.category9 OR badge.category8 = area.category10 OR badge.category9 = area.category1 OR badge.category9 = area.category2 OR badge.category9 = area.category3 OR badge.category9 = area.category4 OR badge.category9 = area.category5 OR badge.category9 = area.category6 OR badge.category9 = area.category7 OR badge.category9 = area.category8 OR badge.category9 = area.category9 OR badge.category9 = area.category10 OR badge.category10 = area.category1 OR badge.category10 = area.category2 OR badge.category10 = area.category3 OR badge.category10 = area.category4 OR badge.category10 = area.category5 OR badge.category10 = area.category6 OR badge.category10 = area.category7 OR badge.category10 = area.category8 OR badge.category10 = area.category9 OR badge.category10 = area.category10 OR badge.category11 = area.category1 OR badge.category11 = area.category2 OR badge.category11 = area.category3 OR badge.category11 = area.category4 OR badge.category11 = area.category5 OR badge.category11 = area.category6 OR badge.category11 = area.category7 OR badge.category11 = area.category8 OR badge.category11 =
3.23.36 index problem(error 136)
Hi, We were running 3.23.21, which had the problem where the index file could only go to 64MB. So I shutdown the db, move the mysql directory(/usr/local/mysql) and installed 3.23.36, and moved the database directories into the new version's directory. I then repaired the radacct table. My problem is that the exact thing is happening with 3.23.36. It gets up to around 64MB and it stops(with table error 136): -rw-r- 1 mysqlmysql67009536 Apr 20 14:00 /usr/local/mysql/var/radius/radacct.MYI If I do a "REPAIR TABLE radacct QUICK", the index shrinks some and it works again, but as soon as it gets back up to 67MB it stops working again with the 136 error. System has plenty of space so that's not a problem. OS: Linux 2.2.19 on i386 Thanks for any help! Ole Gjerde Mimer AS - 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
Myisamchk unable to repair index problem
running myisamchk against basic table gives this error : stopword doesn't have a correct index definition. you need to recreate it before you can do a repair. table details: create table stopword(word char(32) DEFAULT '' NOT NULL, lang char(2) DEFAULT '' NOT NULL, PRIMARY KEY (word, lang)); This create command look good to me, so what could be the problem? I have already done a drop index, create index, flush tables but it still remains. FS - 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
