Re: Indexes issue importing tablespaces
Did you check if an ANALYZE TABLE is enough in this case? -- Wagner Bianchi Mobile: +55.31.8654.9510 Em 10/10/2014, às 09:06, Ruben Cardenal my...@ruben.cn escreveu: Hi, I have this problem among several different instaces of 5.6.20. I take all the steps as stated in http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html and get no errors neither warnings, neither in the cli or the mysql log. (13:23:02) [borrame] alter table creditLine discard tablespace; Query OK, 0 rows affected (0.30 sec) (copy operation of the .cfg and .ibd files from the origin server) (13:23:19) [borrame] alter table creditLine import tablespace; Query OK, 0 rows affected (44.35 sec) 2014-10-10 13:26:42 1657 [Note] InnoDB: Importing tablespace for table 'letsbonus/creditLine' that was exported from host 'dualla' 2014-10-10 13:26:42 1657 [Note] InnoDB: Phase I - Update all pages 2014-10-10 13:27:04 1657 [Note] InnoDB: Sync to disk 2014-10-10 13:27:25 1657 [Note] InnoDB: Sync to disk - done! 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase III - Flush changes to disk 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase IV - Flush complete 2014-10-10 13:27:26 1657 [Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 0 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 0 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) I have to optimize or null-alter the table to get them working: (13:27:34) [borrame] alter table creditLine engine = InnoDB; Query OK, 0 rows affected (12 min 57.41 sec) Records: 0 Duplicates: 0 Warnings: 0 (13:51:17) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 32237680 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 16118840 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 1792 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 8967 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 2 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 293069 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) Is this a know issue? Or something I'm missing? I've checked the doc and saw nothing related to this. Thanks, Rubén.
Re: Indexes issue importing tablespaces
Hi Wagner, Yes! Analyze solves the situation in a moment. (14:21:09) [borrame] alter table creditLine discard tablespace; Query OK, 0 rows affected (0.41 sec) (14:21:21) [borrame] alter table creditLine import tablespace; Query OK, 0 rows affected (23.48 sec) (14:24:55) [borrame] analyze table creditLine; ++-+--+--+ | Table | Op | Msg_type | Msg_text | ++-+--+--+ | borrame.creditLine | analyze | status | OK | ++-+--+--+ 1 row in set (0.16 sec) (14:25:09) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 32237680 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 16118840 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 5050 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 8161 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 1794 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 64995 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 4 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) (14:25:14) [borrame] Thanks, Rubén. El 2014-10-10 14:19, Wagner Bianchi escribió: Did you check if an ANALYZE TABLE is enough in this case? -- WAGNER BIANCHI MOBILE: +55.31.8654. [2]9510 Em 10/10/2014, às 09:06, Ruben Cardenal my...@ruben.cn escreveu: Hi, I have this problem among several different instaces of 5.6.20. I take all the steps as stated in http://dev.mysql.com/doc/refman/5.6/en/tablespace-copying.html [1] and get no errors neither warnings, neither in the cli or the mysql log. (13:23:02) [borrame] alter table creditLine discard tablespace; Query OK, 0 rows affected (0.30 sec) (copy operation of the .cfg and .ibd files from the origin server) (13:23:19) [borrame] alter table creditLine import tablespace; Query OK, 0 rows affected (44.35 sec) 2014-10-10 13:26:42 1657 [Note] InnoDB: Importing tablespace for table 'letsbonus/creditLine' that was exported from host 'dualla' 2014-10-10 13:26:42 1657 [Note] InnoDB: Phase I - Update all pages 2014-10-10 13:27:04 1657 [Note] InnoDB: Sync to disk 2014-10-10 13:27:25 1657 [Note] InnoDB: Sync to disk - done! 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase III - Flush changes to disk 2014-10-10 13:27:26 1657 [Note] InnoDB: Phase IV - Flush complete 2014-10-10 13:27:26 1657 [Note] InnoDB: borrame.creditLine autoinc value set to 87313435 After this, the indexes look empty: (13:27:26) [borrame] show index from creditLine; ++++--+--+---+-+--++--++-+---+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++++--+--+---+-+--++--++-+---+ | creditLine | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idClient | 1 | idClient | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCreditSubTypology | 1 | idCreditTypology | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idCountry | 1 | idCountry | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | creditLine_idAffiliate | 1 | idAffiliate | A | 0 | NULL | NULL | | BTREE | | | | creditLine | 1 | endDate | 1 | endDate | A | 0 | NULL | NULL | YES | BTREE | | | | creditLine | 1 | status | 1 | status | A | 0 | NULL | NULL | | BTREE | | | ++++--+--+---+-+--++--++-+---+ 7 rows in set (0.00 sec) I have to optimize or null-alter the table to get them working: (13:27:34) [borrame] alter table
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Thanks again :-) Nunzio From: Joerg Bruehe joerg.bru...@oracle.com To: Nunzio Daveri nunziodav...@yahoo.com; mysQL General List mysql@lists.mysql.com Sent: Fri, July 30, 2010 1:31:54 PM Subject: Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?) Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603
Re: Indexes larger than RAM (was: Do you know who can answer this question I posted yesterday please?)
Nunzio Daveri, Joerg Bruehe gave you a lot of good tips to try and speed things up. A few hundred queries per second seem to be a relatively small number to cause the server to crawl. I don't have the rest of your thread, but can you publish some of the slow queries (see Slow Query Log) and the table structure? Mike At 01:31 PM 7/30/2010, you wrote: Hi! I am no InnoDB and tuning expert, so I had intended to stay away from this question. Ok, I'll give some general remarks: Nunzio Daveri schrieb: [[...]] All, I was running slamdb against one of our QA boxes and noticed that the innodb database is 190Gb in size BUT the worrying issue is that the indexes are 30GB in size!!! When I hit this server hard, it tanks on memory but still performs, slower of course ;-) Having indexes which are larger than RAM is (in itself) not critical. IMO, it becomes bad only when accesses to these indexes are spread so wide that even the index pages become subject to frequent IO. Any suggestions on what I should do? I am thinking of doing one of these: Whether any action is needed, and which, depends on the problem you experience: - If the system as a whole (both CPU and disk) has a significant idle percentage, it isn't yet maxed out, and I don't expect that adding resources would improve performance significantly. - If your CPUs have significant waiting for IO percentage, then data accesses need speedup. This could be done by faster disks, but I would expect more results from adding RAM for larger caches. This holds especially if your disk throughput is close to the possible maximum. (Assuming your bulk work is read/select. If it is insert/update, then *removing* indexes might reduce the workload, as there are fewer indexes to maintain.) - If your CPUs are busy, then I don't expect any increase of caching would help. 1. Remove all queries, run for a few days, look at the slow query logs and then find those queries that really need them and index those specificially for performance. Makes sense (only) if you have indexes which aren't really helpful for accesses, so they just add maintenance load. If you do few inserts/updates, an unused index should be paged out and not do much harm. Comes with the cost of reduced performance during that test time, and the need to rebuild the essential indexes afterwards. Has the benefit of getting rid of unused indexes (which just cause maintenance load). 2. Split the single server into two servers both with 16 gb and 2 quad core cpu's. One master the other a slave. Makes sense if your CPUs are busy, *and* you can distribute the read accesses to the two servers (= most accesses are select). If most load is insert/update, I don't expect a real improvement. Biggest cost in hardware and admin effort, so I would do this only after a decent analysis. OTOH, it gives you some (hardware) fault tolerance, this could be an important argument depending on your requirements. 3. Just add another 16gb (32GB total) and that should take care of the indexing issue. Makes sense if the disks are the bottleneck (CPUs are in waiting for IO), so that larger caches will avoid disk accesses. Assumes your machine supports that amount of RAM (many mainboards have a limit at 16 GB, AIUI). Anyone had this problem before??? Oh this is a single box, 100% mysql only and it talks to 3 front end iPlanet web servers that hit it with a few hundread queries per second. For a specific answer, the distribution of accesses between read and write is needed, as well as information which resource is close to the limit. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com (+49 30) 417 01 487 ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: indexes and speeds
There is a huge difference !!! When You Load a Table with a Primary Key, the Primary get built automatically. Not even ALTER TABLE tbl-name DISABLE KEYS has an effect since it disables non-unique indexes. Hence, loading the table is a one-pass operation. In contrast, loading a table with two non-unique indexes in the way you specified has the following effect: Your first statement: create index index_name1 on table_name (/|index_col_name|/1); This will make a temp table, copy the data from your table to the temp table, then build index_name1 on the temp table. Finally it renames the temp table to your table. Your second statement: create index index_name2 on table_name (/|index_col_name|/2); This will make a temp table, copy the data from your table to the temp table, then build index_name1 AND index_name2 on the temp table. Finally, it renames the temp table to your table. It copies the whole table twice. Notice, it build indexes thrice (3 times) not twice. Here is a chart that shows how many index builds would occur it you built indexes one at a time: IndexesTemp Table Loads Index Builds --- 1 1 1 2 2 3 3 3 4 4 4 10 5 5 15 6 6 21 7 7 28 8 8 36 9 9 45 10 10 55 11 11 66 12 12 78 13 13 91 14 14105 15 15120 16 16136 17 17153 N N N(N+1)/2 Building indexes one at a time, and non-unique indexes especially, is very inefficient. If you have non-unique indexes in your table, disable the keys first. Here is an example: CREATE TABLE t1 ( A INT NOT NULL PRIMARY KEY, B INT, C INT ); ALTER TABLE t1 ADD INDEX index_name1 (B); ALTER TABLE t1 ADD INDEX index_name2 (C); ALTER TABLE t1 DISABLE KEYS; -- Shuts off non-unique indexes Load data into t1 -- Primary Key is Loaded ALTER TABLE t1 ENABLE KEYS; -- Non-unique indexes are loaded linearly This is how mysqldumps are reloaded. Just do a mysqldump of a small table and look at the code it generates for any one table. Here is a sample mysqldump: -- -- Table structure for table `contact` -- DROP TABLE IF EXISTS `contact`; CREATE TABLE `contact` ( `sno` int(11) NOT NULL auto_increment, `Name` varchar(50) default NULL, `mobile` varchar(20) default NULL, `email` varchar(100) default NULL, `companyname` varchar(100) default NULL, `newsletterflag` tinyint(4) default NULL, `smsflag` tinyint(4) default NULL, `createdatetime` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`sno`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `contact` -- LOCK TABLES `contact` WRITE; /*!4 ALTER TABLE `contact` DISABLE KEYS */; INSERT INTO `contact` VALUES (1,'[EMAIL PROTECTED]','6421510248','Srinivasa Rao Munagala','THL INDIA ',1,NULL,'2007-11-08 21:48:36'); /*!4 ALTER TABLE `contact` ENABLE KEYS */; UNLOCK TABLES; Your best bet is the disable keys, load the data, and enable keys. Here is a direct quotation from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. While the non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them -Original Message- From: kalin m [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 5:55 PM To: mysql@lists.mysql.com Subject: indexes and speeds hi all... just wondering what is the performance difference between: PRIMARY KEY [/|index_type|/] (/|index_col_name|/1,/|index_col_name|/2) at the time of the table creation or create index index_name1 on table_name (/|index_col_name|/1); create index index_name2 on table_name (/|index_col_name|/2); after the table has been made? the question i guess is: is there performance advantage to have a primary key
Re: indexes and size
i believe date is a reserved word for mysql. don't use reserved word for the column name. it's confused. On Tue, 20 Mar 2007, Peter wrote: Hello, I have a a large a table which a field called date, type date. When I select a smaller range e.g 3 months system uses the index 'date'. That is for let's say 2 million rows. If I select wider date range mysql stops using key. It says possible key date, but do not use it and goes over all 28 Million rows. If I use force index the query becomes even slower. Table type is Myisam. Please advise what should I tune so mysql uses the index without force index and query becomes faster. Thanks :-) Peter -- 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: indexes and size
Hiep Nguyen wrote: i believe date is a reserved word for mysql. don't use reserved word for the column name. it's confused. Using reserved words may be confusing, but it is fine as long as you put them between backticks, as I'm sure the OP is aware of. On Tue, 20 Mar 2007, Peter wrote: Hello, I have a a large a table which a field called date, type date. When I select a smaller range e.g 3 months system uses the index 'date'. That is for let's say 2 million rows. If I select wider date range mysql stops using key. It says possible key date, but do not use it and goes over all 28 Million rows. If I use force index the query becomes even slower. Table type is Myisam. Please advise what should I tune so mysql uses the index without force index and query becomes faster. Thanks :-) Peter -- 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: indexes and size
You need do SHOW INDEXES FROM tbl-name; You will see a column called Cardinlaity (another word for a unique count). This tells you how many distinct values exists at the given Seq_in_index for the index. If the Cardinality of an index is low in relation to the number of rows in the table, then MySQL may decide not to use the index if the number of rows expected to return is a sizeable percentage of the number of rows in the table. Example: 3 month query bring back 200 rows. Total rows ion the table: 2800. 200 / 2800 = 0.07142857142857... = 7.143 % Your 3 month query retrieves 7.143% of the data. Think about: Queryiong 3 months retrieves 7.143 % of the data. Theoretically, 6 months should retrieve 14.286% ( 400 rows) Theoretically, 12 months should retrieve 28.571% ( 800 rows) Theoretically, 18 months should retrieve 42.857% (1200 rows) Theoretically, 24 months should retrieve 57.143% (1600 rows) The closer the cardinality of your query results is to the cardinality of the table, The larger the data range, the larger the number of rows retrieved. More than likely, MySQL decided that a table scan would be faster and resource economical that doing a heavy index scan. You need run this query SELECT (2800/COUNT(DISTINCT date)) RowsPerDate FROM tbl-name; This will tell you the average number of rows in the table per date. You may want to add more columns to the index and your query also. A single index with just a date is not good when the cardinality of the date is low. - Original Message - From: Peter [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 20, 2007 9:24:25 AM (GMT-0500) Auto-Detected Subject: indexes and size Hello, I have a a large a table which a field called date, type date. When I select a smaller range e.g 3 months system uses the index 'date'. That is for let's say 2 million rows. If I select wider date range mysql stops using key. It says possible key date, but do not use it and goes over all 28 Million rows. If I use force index the query becomes even slower. Table type is Myisam. Please advise what should I tune so mysql uses the index without force index and query becomes faster. Thanks :-) Peter -- 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: indexes
Hello. Right, I agree with you for MyISAM tables, however, in my opinion, for InnoDB indexes should be created before populating the table (the link below related to LOAD DATA INFILE statement, but I think, the same applies for INSERT as well): http://lists.mysql.com/mysql/181445 Scott Noyes [EMAIL PROTECTED] wrote: Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, Speed of INSERT Statements. The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. (ibid) On 8/4/05, Michael Stassen [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: indexes
Sebastian wrote: is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? Michael Stassen wrote: It's probably better to create the indexes up front (assuming you know in advance which ones will be needed), but I think that's a moot point if your table already exists and is full of data. There's no sense starting from scratch when you can simply add the index with ALTER TABLE yourtablename ADD INDEX name (item, type); Scott Noyes wrote: Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, Speed of INSERT Statements http://dev.mysql.com/doc/mysql/en/insert-speed.html. The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. (ibid) Sort of. First, this doesn't apply to InnoDB tables. Thus, if your table is MyISAM, and you intend to create the table and fill it with data all at once, it is faster to create the table with indexes, disable them, load the data, then enable the indexes, as described on the page you referenced. Note also that you don't need myisamchk if you use LOAD DATA INFILE to populate an empty table, or you can use ALTER TABLE tablename DISABLE KEYS before inserting and ALTER TABLE tablename ENABLE KEYS after. This trick is also good for bulk inserts later. The basic idea is that building/updating an index is a cost best paid as few times as possible. Best case is to update the index all at once after all data has been inserted. Worst case is to use single row inserts, updating the index after each. The preceding only applies to MyISAM tables which can be filled when created, or where later inserts can be done in bulk. If your table grows as customers use it, however, it's not very helpful, as it makes no sense to disable/enable keys around a single row insert. I intended my reply to be specific to the original poster's issue, existing full table with slow queries, rather than as general advice. I meant that adding indexes in the beginning is better (planning ahead) than adding them down the road when queries are slowing because there aren't any indexes (reacting). I did not mean to make a statement about speed of creating pre filled tables. I agree I was unclear. It also seems I misunderstood the situation, because Sebastian wrote: i am merging about 3 tables (each about 10-15k rows) into one table, so that is why i asked about when i should create the index (before or after) since i will be moving things around. since i have 3 tables which all are similar it is a pain because i have to repeat a lot of code in the app... this way i can just fetch the rows i need by specifying the item and type for the apps. so it's a new table with no data yet which i will dump into from other tables. In that case, Scott's advice is apt. This will go fastest if you follow the directions in the manual page he cites. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes
i forgot to add another question, is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? Sebastian wrote: I read the manual on indexes but i am a little confused how i should build the type of index i need. i have this structure: | id | item | type || 3 || 23 || news || 4 || 46 || faqs || 5 || 23 || news || 6 || 23 || news query: SELECT id, item, type ... FROM comments WHERE item = 23 AND type = 'news' i want a mulitple column index in this case i assume? INDEX name (item, type); is this correct? anything else i should know? i didn't pay attention to query speed until the site started to explode, so i need to speed up queries i created 2 years ago. thanx. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes
Sebastian wrote: I read the manual on indexes but i am a little confused how i should build the type of index i need. i have this structure: | id | item | type || 3 || 23 || news || 4 || 46 || faqs || 5 || 23 || news || 6 || 23 || news query: SELECT id, item, type ... FROM comments WHERE item = 23 AND type = 'news' i want a mulitple column index in this case i assume? INDEX name (item, type); is this correct? anything else i should know? i didn't pay attention to query speed until the site started to explode, so i need to speed up queries i created 2 years ago. thanx. The right index to add depends on your queries and the existing indexes. Do you already have any indexes on your table? Adding an index dramatically speeds up queries that use it, but at the cost of slower inserts (each index must be updated) and larger file size (indexes take space). Thus, the ideal is to have the minimum set of indexes required for your typical queries. Your sample query might be helped by a single-column index on item or type, depending on how restrictive the conditions item = 23 and type = 'news' are. The multiple-column index on (item, type) you describe would be best for this particular query (assuming there are more unique items than types), and others like it that restrict both item and type in the WHERE clause. It would also function as a single-column index on item, so if you already have one of those, it could be droppped when you add the multi-column index. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes
Sebastian [EMAIL PROTECTED] wrote on 08/04/2005 01:31:13 PM: i forgot to add another question, is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? Sebastian wrote: I read the manual on indexes but i am a little confused how i should build the type of index i need. i have this structure: | id | item | type || 3 || 23 || news || 4 || 46 || faqs || 5 || 23 || news || 6 || 23 || news query: SELECT id, item, type ... FROM comments WHERE item = 23 AND type = 'news' i want a mulitple column index in this case i assume? INDEX name (item, type); is this correct? anything else i should know? i didn't pay attention to query speed until the site started to explode, so i need to speed up queries i created 2 years ago. thanx. Yes, that two-column index will most likely speed up that type of query. If there is a 3rd column that frequently appears in the WHERE clauses of your queries along with `item` and `type`, you should probably consider making your index out of all 3 columns (with the 3rd column last) rather than just the two columns you already identified. is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? For bulk data loading and other INSERT/UPDATE operations that affect large numbers of rows (especially when building your table the first time), you should be able to setup your data much faster without the indexes in place. Also, since you are creating/modifying the indexes only once (and in bulk) it should take less TOTAL TIME (populate time + indexing time) with later indexing than if you had the indexes in place during the initial load. I am sorry you waited so long to optimize your queries but better now than later. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: indexes
Sebastian wrote: i forgot to add another question, is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? It's probably better to create the indexes up front (assuming you know in advance which ones will be needed), but I think that's a moot point if your table already exists and is full of data. There's no sense starting from scratch when you can simply add the index with ALTER TABLE yourtablename ADD INDEX name (item, type); Be aware, however, that mysql alters a table by making a new, temporary table to match the new definition (old def + alterations), fills it with data from the original table, then replaces the old with the new. The time to do this grows with the size of your table and number of indexes. While it is in progress, statements which would change the data (e.g. INSERT, UPDATE) are locked out. See the manual for details http://dev.mysql.com/doc/mysql/en/alter-table.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes
Some sections of the manual seem to indicate that it's better to create indexes after data population, rather than before. See section 7.2.14, Speed of INSERT Statements. The general procedure there is to load the data using LOAD DATA INFILE, and then use myisamchk. This creates the index tree in memory before writing it to disk, which is much faster because it avoids lots of disk seeks. The resulting index tree is also perfectly balanced. (ibid) On 8/4/05, Michael Stassen [EMAIL PROTECTED] wrote: Sebastian wrote: i forgot to add another question, is it ok to add index after the tables and data are already built, or is it better to create the index before data gets inserted? It's probably better to create the indexes up front (assuming you know in advance which ones will be needed), but I think that's a moot point if your table already exists and is full of data. There's no sense starting from scratch when you can simply add the index with ALTER TABLE yourtablename ADD INDEX name (item, type); Be aware, however, that mysql alters a table by making a new, temporary table to match the new definition (old def + alterations), fills it with data from the original table, then replaces the old with the new. The time to do this grows with the size of your table and number of indexes. While it is in progress, statements which would change the data (e.g. INSERT, UPDATE) are locked out. See the manual for details http://dev.mysql.com/doc/mysql/en/alter-table.html. Michael -- 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: indexes
Michael Stassen wrote: Sebastian wrote: I read the manual on indexes but i am a little confused how i should build the type of index i need. i have this structure: | id | item | type || 3 || 23 || news || 4 || 46 || faqs || 5 || 23 || news || 6 || 23 || news query: SELECT id, item, type ... FROM comments WHERE item = 23 AND type = 'news' i want a mulitple column index in this case i assume? INDEX name (item, type); is this correct? anything else i should know? i didn't pay attention to query speed until the site started to explode, so i need to speed up queries i created 2 years ago. thanx. The right index to add depends on your queries and the existing indexes. Do you already have any indexes on your table? Adding an index dramatically speeds up queries that use it, but at the cost of slower inserts (each index must be updated) and larger file size (indexes take space). Thus, the ideal is to have the minimum set of indexes required for your typical queries. Your sample query might be helped by a single-column index on item or type, depending on how restrictive the conditions item = 23 and type = 'news' are. The multiple-column index on (item, type) you describe would be best for this particular query (assuming there are more unique items than types), and others like it that restrict both item and type in the WHERE clause. It would also function as a single-column index on item, so if you already have one of those, it could be droppped when you add the multi-column index. Michael i am merging about 3 tables (each about 10-15k rows) into one table, so that is why i asked about when i should create the index (before or after) since i will be moving things around. since i have 3 tables which all are similar it is a pain because i have to repeat a lot of code in the app... this way i can just fetch the rows i need by specifying the item and type for the apps. so it's a new table with no data yet which i will dump into from other tables. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.338 / Virus Database: 267.10.0/63 - Release Date: 8/3/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes
A Z [EMAIL PROTECTED] wrote on 02/04/2005 07:21:35 AM: MySql 4.0.14 I understand that the most effective way of speeding up SELECT statements is to have column level indexes. Are there any other level indexes exist? I have been told that there are but I have no idea, looked through the manual, could not find anything that speeds up column level indexing. regards ___ ALL-NEW Yahoo! Messenger - all new features - even more fun! http: //uk.messenger.yahoo.com Creating a good index schema is usually the most effective way to speed up most queries. However all indexes are not used for every query. The query engine chooses up to one index (per table) if it feels that using the index will help query performance. It determines this by checking the cardinality of the index as it relates to your query. The cardinality is related to how many rows the index holds for any particular value (or value range or value set) compared to the size of the index. The higher the cardinality, the more selective an index will be and that generally means that you will get fewer records back in the results of the query. When the engine determines, by looking at the tables statistics, that using an index in a particular query will result in returning approximately 30% or less of all the rows in a table, it will probably use the index. Many new users create very non-selective indexes which will be considered but not used as they end up returning too many rows to be useful (like indexing a true/false column). The reason that the usability threshold of an index is somewhere near 30% is that, for values larger than that number, it would take longer to use an index to get the position of each target row then go get that row from the data than it would to just scan the data directly in the first place. There are at least 2 fewer disk seeks to perform a direct read of the table than for an indexed locate for EACH value retrieved. The key to creating a good index schema is to look at your query patterns. Look specifically at your WHERE clauses, first. If you notice that 80% of your queries use your_column_a and your_column_b as a pair then it would more than likely help you to create an index that covered both of those columns ALTER TABLE your_table_1 ADD INDEX (your_column_a, your_column_b) Because MySQL is smarter than some database engines, any query that only references your_column_a may also use that index (depending on the cardinality). My advice is: A) Avoid single column indexes whenever practical. Most useful indexes contain from 2 to 5 fields. B) Don't forget that PRIMARY keys and UNIQUE constraints are also indexes. C) Design your indexes after your most common or frequently used query patterns. Analyze your WHERE clauses first, then look at speeding up certain queries by considering values in your ORDER BY clauses. D) Learn how to use EXPLAIN. It will give you excellent advice on how to help your queries. E) Sometimes functions in your WHERE clauses eliminate the possibility of using an index. Learn how to say your_column_name comparison function or constant expression rather than function or expression using your_column_name comparison function or constant expression. For example, if you want to find date values in the column logdate that are at least 60 days old, DO NOT use this: WHERE logdate + 60 days CURDATE() use this instead: WHERE logdate curdate() - 60 days F) Read the fine manual. http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/estimating-performance.html http://dev.mysql.com/doc/mysql/en/select-speed.html http://dev.mysql.com/doc/mysql/en/where-optimizations.html http://dev.mysql.com/doc/mysql/en/optimizing-database-structure.html (whole chapter) (especially this part) http://dev.mysql.com/doc/mysql/en/mysql-indexes.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Indexes use different block sizes error with preloading fulltext indexes.
Mark It is a known problem with this feature. So far I am not aware of any solution to it. Just want to let you know that you are not alone having this problem. Haitao On Fri, 05 Nov 2004 02:16:49 +, Mark Maunder [EMAIL PROTECTED] wrote: I keep getting this error when trying to preload a fulltext index. I've checked the block size of the fulltext index using myisamchk (is there an easier way to find out block size?) and it is 2048. The block size of the primary key on the same table is 1024. Is that what it means by Indexes use different block sizes? As you can see from below, I've tried to only load the fulltext index, and the error persists. I have also tried setting the global key_cache_block_size to 2048 and that didn't work. I have also tried creating a separate key cache with it's own 2048 block size and preloading the index into that, and that didn't work either. Any help is appreciated. mysql load index into cache fttest INDEX (ft); ++--+--+---+ | Table | Op | Msg_type | Msg_text | ++--+--+---+ | workzoo.fttest | preload_keys | error| Indexes use different block sizes | | workzoo.fttest | preload_keys | status | Operation failed | ++--+--+---+ 2 rows in set (0.00 sec) Mark. -- 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: Indexes use different block sizes error with preloading fulltext indexes.
OK, thanks. I've reported this as a bug, and another, below. I must admit, this production release seems flakey: http://bugs.mysql.com/bug.php?id=6447 On Fri, 2004-11-05 at 04:03, Haitao Jiang wrote: Mark It is a known problem with this feature. So far I am not aware of any solution to it. Just want to let you know that you are not alone having this problem. Haitao On Fri, 05 Nov 2004 02:16:49 +, Mark Maunder [EMAIL PROTECTED] wrote: I keep getting this error when trying to preload a fulltext index. I've checked the block size of the fulltext index using myisamchk (is there an easier way to find out block size?) and it is 2048. The block size of the primary key on the same table is 1024. Is that what it means by Indexes use different block sizes? As you can see from below, I've tried to only load the fulltext index, and the error persists. I have also tried setting the global key_cache_block_size to 2048 and that didn't work. I have also tried creating a separate key cache with it's own 2048 block size and preloading the index into that, and that didn't work either. Any help is appreciated. mysql load index into cache fttest INDEX (ft); ++--+--+---+ | Table | Op | Msg_type | Msg_text | ++--+--+---+ | workzoo.fttest | preload_keys | error| Indexes use different block sizes | | workzoo.fttest | preload_keys | status | Operation failed | ++--+--+---+ 2 rows in set (0.00 sec) Mark. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark D. Maunder [EMAIL PROTECTED] http://www.workzoo.com/ The Best jobs from the Best Job Sites. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexes
Yes. Those keys are redundant. -Original Message- From: Cemal Dalar To: Group MySQL List Sent: 8/13/04 7:12 AM Subject: Indexes mysql show index from urun; +---++--+--+-+-- -+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+-- -+-+--++--++-+ | urun | 0 | PRIMARY |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 0 | UC_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | IDX_urun_urun_id |1 | urun_id | A | 108725 | NULL | NULL | | BTREE | | | urun | 1 | ktgr |1 | ktgr| A | 512 | NULL | NULL | | BTREE | | +---++--+--+-+-- -+-+--++--++-+ 4 rows in set (0.00 sec) Also my show create table urun looks like this.. .. PRIMARY KEY (`urun_id`), UNIQUE KEY `UC_urun_id` (`urun_id`), KEY `IDX_urun_urun_id` (`urun_id`), KEY `ktgr` (`ktgr`) ) TYPE=MyISAM | Isn't this KEY `IDX_urun_urun_id` (`urun_id`), and UNIQUE KEY `UC_urun_id` (`urun_id`), indexes are unnecessary? Best Regards, Cemal Dalar a.k.a Jimmy System Administrator Web Developer http://www.dalar.net -- 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: Indexes ignored when using SELECT foo FROM a, b?
Use force index to force the index lookup on foo_id
If that doesn't work try analyze table on that table and run the explain
again.
The OR will not allow you to use a compound index but the primary key or 1st
key-foo_id should be used. I just noticed that your table definition foo_id
is not defined as a primary key, so running analyze table will get things
into perspective.
DVP
Dathan Vance Pattishall http://www.friendster.com
-Original Message-
From: Eamon Daly [mailto:[EMAIL PROTECTED]
Sent: Monday, May 17, 2004 7:32 AM
To: [EMAIL PROTECTED]
Subject: Indexes ignored when using SELECT foo FROM a, b?
I have a table structured like so:
CREATE TABLE `foo_equivalency` (
`foo_id` smallint(6) NOT NULL default '0',
`type` enum('a_id','b_id','foo_id') NOT NULL default 'foo_id',
`id` smallint(6) NOT NULL default '0',
KEY `foo_id` (`foo_id`),
KEY `type` (`type`)
) TYPE=MyISAM
I'm using a SELECT to pull all of the equivalent foo_id's
from another table, bar, like so:
SELECT bar.foo_id, foo_equivalency.foo_id FROM bar, foo_equivalency
WHERE
foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND
(type = 'a_id' and id = bar.a_id) OR
(type = 'b_id' and id = bar.b_id) OR
(type = 'foo_id' and id = bar.foo_id)
foo_id is a primary key in table bar, and I created test
indexes on a_id and b_id:
PRIMARY KEY (`foo_id`),
KEY `a_id` (`a_id`),
KEY `b_id` (`b_id`)
EXPLAIN reveals that no indexes are being used:
+-+--+---+--+-+--+
--
+-+
| table | type | possible_keys | key | key_len | ref |
rows
| Extra |
+-+--+---+--+-+--+
--
+-+
| bar | ALL | PRIMARY,a_id,b_id | NULL |NULL | NULL |
269
| |
| foo_equivalency | ALL | foo_id,type | NULL |NULL | NULL |
2931
| Using where |
+-+--+---+--+-+--+
--
+-+
I'm unclear as to why none of the indexes apply, not even
the PRIMARY on foo_id. Is it the ORs that blow everything
away? Can I work around it?
It occurs to me that I could split foo_equivalency into 3
tables (foo_equivalency_by_a, foo_equivalency_by_b, and
foo_equivalency_by_foo), rather than using the enum and a
conditional, but that seems really unwieldy and slow (3
selects and then a UNION). Suggestions? I'm on MySQL 4.0.18,
by the way, so subselects aren't an option.
Eamon Daly
NextWave Media Group LLC
Tel: 1 773 975-1115
Fax: 1 773 913-0970
--
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: Indexes ignored when using SELECT foo FROM a, b?
Another reader pointed out that I actually want to group the ORs together, which allows the query to use the index on foo_id. I've also been experimenting with multiple SELECTs and UNIONs like so: SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.a_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'a_id' UNION SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.b_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'b_id' UNION SELECT bar.foo_id, foo_equivalency.foo_id FROM bar JOIN foo_equivalency ON id = bar.foo_id WHERE foo_equivalency.foo_id IN (367,365,327,269,197,387,379,361,331) AND type = 'foo_id' The EXPLAIN looks slightly better (160+14+1), I think: +-++--+--+-- ---++--+--+ | table | type | possible_keys| key | key_len | ref| rows | Extra| +-++--+--+-- ---++--+--+ | foo_equivalency | range | foo_id,type,foo_and_type | foo_and_type | 3 | NULL | 20 | Using where | | bar | ref| a_id | a_id | 3 | foo_equivalency.id |8 | Using where | | foo_equivalency | range | foo_id,type,foo_and_type | foo_and_type | 3 | NULL |7 | Using where | | bar | ref| b_id | b_id | 3 | foo_equivalency.id |2 | Using where | | foo_equivalency | ref| foo_id,type,foo_and_type | type | 1 | const |1 | Using where | | bar | eq_ref | PRIMARY | PRIMARY | 2 | foo_equivalency.id |1 | Using where; Using index | +-++--+--+-- ---++--+--+ But, of course, those UNIONs still mean a full join. Is this as good as I'm going to get? Thanks for the previous replies! Eamon Daly NextWave Media Group LLC Tel: 1 773 975-1115 Fax: 1 773 913-0970 - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] Sent: Monday, May 17, 2004 12:09 PM Subject: RE: Indexes ignored when using SELECT foo FROM a, b? Use force index to force the index lookup on foo_id If that doesn't work try analyze table on that table and run the explain again. The OR will not allow you to use a compound index but the primary key or 1st key-foo_id should be used. I just noticed that your table definition foo_id is not defined as a primary key, so running analyze table will get things into perspective. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexes and multi-column Primary Keys
Hmmm... I fixed the problem but I still don't know what caused it. That same query now produces the below EXPLAIN result and now runs 70% faster. I ran OPTIMIZE TABLE on Spells and Classes, that made Spl rows 1, then I forced the Cls table to use the Object index and it worked. Now, I can't get it to reproduce the problem. The database was just created and populated, so it couldn't have been DB degradation. Any ideas? Chris +-++--+--+-+-+--+--- ---+ | table | type | possible_keys| key | key_len | ref | rows | Extra | +-++--+--+-+-+--+--- ---+ | NPCSpl | index | PRIMARY | PRIMARY | 10 | NULL | 6034 | Using index; Using temporary; Using filesort | | ObjNPCs | eq_ref | PRIMARY | PRIMARY | 8 | const,NPCSpl.FileID,NPCSpl.ObjectID |1 | Using where; Using index | | NPCs| eq_ref | PRIMARY,ObjectID | PRIMARY | 6 | ObjNPCs.FileID,ObjNPCs.ObjectID |1 | | | Spl | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCSpl.ObjectID_Spell |1 | | | ObjSpl | eq_ref | PRIMARY | PRIMARY | 8 | const,Spl.FileID,Spl.ObjectID |1 | Using where; Using index | | Cls | ref| PRIMARY,ObjectID | ObjectID | 4 | NPCs.ObjectID_Class |1 | Using where | | ObjCls | eq_ref | PRIMARY | PRIMARY | 8 | const,Cls.FileID,Cls.ObjectID |1 | Using where; Using index | +-++--+--+-+-+--+--- ---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INDEXes on BLOB Columns
At 5:29 -0400 9/10/03, Andrew Kuebler wrote: If I: ADD COLUMN Text (BLOB) And then INDEX (Text(10)) Will that index be beneficial if I am search for 10 characters only or anything 10 characters or less? Thanks in advance! If those characters occur at the beginning of column values, yes. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes to speed up a duplicates query.
Here's the full query I'm trying to do: explain select users.status, users.oid as oid, users.type as type, users.exclusive as exclusive, userse.o_initials as exclusive_initials, users.name_processed as name_processed, users.o_company as o_company, unix_timestamp(users.created) as created, count(distinct notes.oid) as notes_num, modifiers.o_initials as modified_by_initials, duplicateusers.oid as duplicate, duplicateusers.name_processed as duplicate_name, duplicateusers.o_company as duplicate_company, unix_timestamp(duplicateusers.created) as duplicate_created, duplicateusers.modified as duplicate_modified, count(distinct duplicatenotes.oid) as duplicate_num_notes fROM users left join users duplicateusers on (duplicateusers.created users.created) left join users userse on users.exclusive=userse.oid left join notes on notes.parent=users.oid left join users modifiers on users.modified_by=modifiers.oid left join notes duplicatenotes on duplicatenotes.parent=duplicateusers.oid where (users.status='O') and (((1=1 and users.type='USER'))) and (soundex(users.o_last_name)=soundex(duplicateusers.o_last_name) or (users.o_email_address=duplicateusers.o_email_address)) group by oid --- Plus a supplementary question: whether I have 'limit 1' or 'limit 100' doesn't seem to make any difference. I thought it must be my 'order by' clause, but I got rid of that no change. - this is Tom Cunningham. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes question
In the last episode (Jun 01), Daniel Rossi said: hi just reading up on optimising indexes, does it matter what order u set your indexes ? The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index. i dont really understand what they meant by this , i use most the columns when searching but only a few of the columns which are fulltext get searched the most , do i add these indexes first then ? That part of the manual is talking about a single multi-column index. If you have multiple separate indexes, it doesn't matter in which order you create them. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexes question
i have one multiple column fulltext index for 3 columns i join in a fulltext match statement In the last episode (Jun 01), Daniel Rossi said: hi just reading up on optimising indexes, does it matter what order u set your indexes ? The first index part should be the most used column. If you are always using many columns, you should use the column with more duplicates first to get better compression of the index. i dont really understand what they meant by this , i use most the columns when searching but only a few of the columns which are fulltext get searched the most , do i add these indexes first then ? That part of the manual is talking about a single multi-column index. If you have multiple separate indexes, it doesn't matter in which order you create them. -- Dan Nelson [EMAIL PROTECTED] -- 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: indexes question
In the last episode (Jun 01), [EMAIL PROTECTED] said: i have one multiple column fulltext index for 3 columns i join in a fulltext match statement Fulltext indexes are different from regular indexes. I don't think it matters what order the columns are in. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexes
At 4:41 + 3/6/03, Donna Robinson wrote: Filter-fodder: mysql, query Hi, Is this a very hard question or an absurdly simple one? I posted it days ago, and no-one has responded! Please can someone help? How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Donna Maybe none of understand what your question means? I know I don't. - 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: Indexes
On Tuesday 04 March 2003 09:50, Donna Robinson wrote: How can I avoid Using temporary, Using filesort when creating indices when my selects MUST be ordered? Please, check chapter 5 MySQL Optimisation of the manual. You can find cases when MySQL uses indexes and when it doesn't use them: http://www.mysql.com/doc/en/MySQL_Optimisation.html -- 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 - 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: Indexes That Span Columns...
At 14:12 -0500 12/20/02, Andrew Kuebler wrote: I have the following table; I renamed the columns a b for easy reading: ID INT(9) UNSIGNED AUTO_INCREMENT PRIMARY KEY, aID INT(9), bID MEDIUMINT(7), UNIQUE ab (aID, bID) INDEX b(bID) As far as I understand the UNIQUE INDEX also provides the index for aID, Right, although it won't necessarily be a unique index. but not bID. Is there any other way to have a UNIQUE INDEX across aID bID that will also provide an INDEX individually for the 2, or do I still have to build a second INDEX separately just for bID? The latter; you need a separate index on bID. I'm using MySQL 4.0.1, maybe a new version has a better way to do this? Thank you in advance... Andrew - 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: indexes
David Churches wrote: Hi, I am using mySQL version 3.23.52 and I have tried to create an index on a large table. This was taking a very long time and so the command was terminated. Now the database seems to be corrupted, or at least inaccessible. Is there anything I can do? I have tried mysqlcheck but it seems to be taking a very long time too. Thanks in advance for any help you can give, Dave. - David Churches Department of Physics and Astronomy Cardiff University, 5 The Parade, Cardiff, CF24 3YB, U.K. Phone: + 44-29-20874785, 20875120 (direct line) Fax: + 44-29-20874056 [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 David, Check the archives. There was some discussion on this earlier this week. Here is some info from one of the discussions. Also, use myisamchk -dv tablename Pay attention to datafile pointer (bytes) and Keyfile pointer (bytes) and the Max keyfile/datafile length. The keyfile pointer must be at least 4 bytes for a large table, if it says 3 you have a problem! This is isn't clear from the CREATE TABLE section of the manual, but is in the 1.2.4 How Big Can MySQL Tables Be?. I don't know if there is any problem or change like this need for InnoDB, since Innodb is not mentioned I would suspect it is not needed. walt - 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: indexes
David, Thursday, October 24, 2002, 11:27:30 AM, you wrote: DC I am using mySQL version 3.23.52 and I have tried to create an index on a DC large table. This was taking a very long time and so the command was DC terminated. Now the database seems to be corrupted, or at least DC inaccessible. Is there anything I can do? CREATE INDEX on a large table is not a quick operation. How large is your table? How long creation continued before you terminated it? What is the type of the table? DC I have tried mysqlcheck but it seems to be taking a very long time too. You terminated myisamchk, too? What do you mean inaccessible? Do you get any error? Please, be more detailed .. -- 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 - 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: indexes
Hello Egor, I am not sure how large the table is, since I cannot access it now. I would guess that it has at least 1 million rows, and maybe more. It is a MyISAM table type. I probably terminated the create index command after about 1 hour. Afterwards, when I type 'use database_name' on that database, it seems to get stuck, i.e. it says the following and then no more: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A I have not terminated the mysqlcheck, it is still running but giving no feedback - the actual command was mysqlcheck database_name --fast -v Cheers, David. - David Churches Department of Physics and Astronomy Cardiff University, 5 The Parade, Cardiff, CF24 3YB, U.K. Phone: + 44-29-20874785, 20875120 (direct line) Fax: + 44-29-20874056 [EMAIL PROTECTED] On Thu, 24 Oct 2002, Egor Egorov wrote: David, Thursday, October 24, 2002, 11:27:30 AM, you wrote: DC I am using mySQL version 3.23.52 and I have tried to create an index on a DC large table. This was taking a very long time and so the command was DC terminated. Now the database seems to be corrupted, or at least DC inaccessible. Is there anything I can do? CREATE INDEX on a large table is not a quick operation. How large is your table? How long creation continued before you terminated it? What is the type of the table? DC I have tried mysqlcheck but it seems to be taking a very long time too. You terminated myisamchk, too? What do you mean inaccessible? Do you get any error? Please, be more detailed .. -- 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 - 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: Indexes
On Tue, 2002-06-18 at 13:48, Alexander Burbello wrote: I would like to know if mysql have any resources to know when I execute any sql (eg. select) and I would like to know what index was used, like SQL server. See EXPLAIN at http://www.mysql.com/doc/E/X/EXPLAIN.html -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ 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
RE: Re: Indexes on UPDATE/DELETE
Also, how do I reset an auto-increment value back to 1 (with all rows being already deleted of course). The following deletes all records and re-starts renumbering from 1; DELETE FROM mytable; The following deletes all records and continues counting from where the table last left off: DELETE FROM mytable WHERE 1; -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Saturday, February 09, 2002 4:50 PM To: [EMAIL PROTECTED] Subject: Re: Re: Indexes on UPDATE/DELETE sql,query At 15:35 -0700 2/9/02, Andrei Cojocaru wrote: Also, how do I reset an auto-increment value back to 1 (with all rows being already deleted of course). ALTER TABLE tbl_name AUTO_INCREMENT = 1; Thanks for your help in advance. - 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: Re: Indexes on UPDATE/DELETE
sql,query At 15:35 -0700 2/9/02, Andrei Cojocaru wrote: Also, how do I reset an auto-increment value back to 1 (with all rows being already deleted of course). ALTER TABLE tbl_name AUTO_INCREMENT = 1; Thanks for your help in advance. - 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: Re: Indexes on UPDATE/DELETE
sql,query At 15:35 -0700 2/9/02, Andrei Cojocaru wrote: Also, how do I reset an auto-increment value back to 1 (with all rows being already deleted of course). ALTER TABLE tbl_name AUTO_INCREMENT = 1; Thanks for your help in advance. - 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: Indexes in temporary tables
Goran Krajacic writes: I wonder if mysql uses indexes in temporary tables(when making a join on temp tables etc.)? MySQL treats temp tables same as normal ones, except when the thread ends ... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ 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
Re: indexes and desc
On Mon, Sep 10, 2001 at 09:13:06AM -0700, PR wrote: And are there future plans for indexing on desc order? This is already fixed in MySQL 4.0. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 4 days, processed 99,159,321 queries (254/sec. avg) - 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: Indexes on large tables
Scott Chamberlain wrote: When I try to create an index on any table with greater than about 200,000 records, it hangs using show processlist shows the process to have died. I have even tried moving the data out of the table, creating the index on the empty table and moving the data back in again, but the process moving the data back also dies. Has anyone seen anything like this before ? Thanks in anticipation Scott Chamberlain Hi, I have been adding and deleting indexes on a table with ~250,000 records for benchmarking purposes lately and it worked fine. However, maybe you are not waiting long enough to let it finish. I am currently doing it with a 'key_buffer' parameter big enough to hold all indexes in memory (128Mb in my case) and adding or deleting an index takes around 2 minutes. When I did the same thing with a smaller key_buffer (16 Mb), I killed mysqld after 1h30 and it had not finished adding a new index ! Hope this helps -- Joseph Bueno NetClub/Trader.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: Indexes on Nulls
Dennis, Innobase (= InnoDB starting from version 3.23.37) allows you to have an index on a column which may contain nulls. However, the ANSI SQL specification requires that the columns in a primary key are declared as not null. If your main key may can contain nulls, do not define a primary key at all, only an ordinary index. Example: CREATE TABLE D1 (A INT, B INT, INDEX (A)) TYPE = INNOBASE; Regards, Heikki "DG" == Dennis Gearon [EMAIL PROTECTED] writes: DG Is it true no indexing on Nulls In the Innobase database tables and DG MyIASMDG tables? Dunnow about Innobase tables, but MyISAM tables let you have indexes on nullable columns. ISAM did not.-- - 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: Indexes on Nulls
"DG" == Dennis Gearon [EMAIL PROTECTED] writes: DG Is it true no indexing on Nulls In the Innobase database tables and DG MyIASM DG tables? Dunnow about Innobase tables, but MyISAM tables let you have indexes on nullable columns. ISAM did not. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ - 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: indexes on TEMPORARY HEAP tables ignored in ORDER BY / GROUP BY
For some strange reason, all HEAP table indexes only process WHERE clauses with = and = operators ONLY. There's nothing you can do about it. I'm pretty sure of that because I remember the MySQL mentions something like this about HEAP tables. -Original Message- From: Jeff S Wheeler [mailto:[EMAIL PROTECTED]] Sent: March 8, 2001 9:11 PM To: [EMAIL PROTECTED] Subject: indexes on TEMPORARY HEAP tables ignored in ORDER BY / GROUP BY Hi, I'm not subscribed to the list so please CC: me on replies. Why are indexes on TEMPORARY tables created with TYPE=HEAP ignored when doing a query involving ORDER BY or GROUP BY? These indexes are used in some selects involving a WHERE clause, so obviously the index isn't completely worthless. Why is it not used in cases when the index would be used if it were an on-disk table? My disk subsystem is not fast enough to do these queries efficiently, but I do have enough memory to create the temp table with TYPE=HEAP. But, it would improve performance signifigantly if indexes were not ignored. I know this because if I do create a disk table and then query against it while it is in os-disk-cache, the queries are actually faster than the TYPE=HEAP table, because mysql will use the index effectively. Thanks, SQL statements and .sig to follow. mysql CREATE TEMPORARY TABLE t2 (CallCount INT NOT NULL, BTN CHAR(24) NOT NULL) TYPE=HEAP; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t2 SELECT COUNT(*) AS CallCount, BillN AS BTN FROM Call GROUP BY BillN; Query OK, 5617334 rows affected (40.50 sec) mysql CREATE INDEX CallCount ON t2 (CallCount); Query OK, 5617334 rows affected (13.76 sec) mysql EXPLAIN SELECT * FROM t2 USE INDEX (CallCount) ORDER BY CallCount DESC LIMIT 10; +---+--+---+--+-+--+-+-- --+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +---+--+---+--+-+--+-+-- --+ | t2| ALL | NULL | NULL |NULL | NULL | 5617334 | Using filesort | +---+--+---+--+-+--+-+-- --+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM t2; +---++---+--+-+---+- +--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++---+--+-+---+- +--++-+ | t2| 1 | CallCount |1 | CallCount | NULL | NULL | NULL | NULL | | +---++---+--+-+---+- +--++-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT * FROM t2 WHERE CallCount=10; +---+--+---+---+-+---+--+--- -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+---+-+---+--+--- -+ | t2| ref | CallCount | CallCount | 4 | const | 10 | where used | +---+--+---+---+-+---+--+--- -+ 1 row in set (0.00 sec) --- Jeff S Wheeler [EMAIL PROTECTED] Software DevelopmentFive Elements, Inc http://www.five-elements.com/~jsw/ - 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
