Re: problem forcing indexes
On Jan 3, 2008 4:23 PM, Tanner Postert [EMAIL PROTECTED] wrote: When I try to add a force index (PRIMARY) after the media table to try and make is use PRIMARY, rather than TYPE, the optimizer switches and uses no key at all. It usually knows better than you do about indexes. I've tried to change the order in which the tables are selected, but it seems to have no effect. It should be able to choose the best order most of the time. You can force it, but that's nearly always a mistake. In some scenarios it will switch and use the media_views table, but the rows is still 125,000+ using temporary and filesort. For this relatively small result set, temporary and filesort may not be a big deal. They are probably being used to handle your ORDER BY. how can I get this query time down? You can try some combined indexes, like one on media (id, status, type, created) and one on media_views (media_id, 24h). I don't think you can eliminate the temp table with that ORDER BY though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. Thanks. -- James Lamanna -- 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: Problem with indexes
Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
I am assuming that the Memo field is a text datatype and you were encountering the duplicate key bug. Original Message On 2/25/04, 3:28:58 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Re: Problem with indexes: Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with indexes
Yes. [EMAIL PROTECTED] wrote: I am assuming that the Memo field is a text datatype and you were encountering the duplicate key bug. Original Message On 2/25/04, 3:28:58 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Re: Problem with indexes: Found out that the problem was actually a bug in 4.0.17. Upgrading to 4.0.18 seems to have fixed the problem. Noted here: http://bugs.mysql.com/bug.php?id=2446 [EMAIL PROTECTED] wrote: Can you do a show create table Table1 and see how the index is defined? Original Message On 2/25/04, 12:02:43 PM, James Lamanna [EMAIL PROTECTED] wrote regarding Problem with indexes: So I'm having issues with indexes in mysql 4.0 I issue the following: Create Index Index1 on Table1 (ParentID,ClassID,Amount,Memo(20)); That works fine. However I'm running into problems when I'm trying to insert into this table using INSERT I'm receiving errors about duplicate keys against this Index. I thought this Index should be non-unique since I didn't specify the UNIQUE keyword. This table does not have a Primary Key either. Kinda need help ASAP, so any ideas would be appreciated. -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- James Lamanna Applied Minds, Inc. 1209 Grand Central Ave. Glendale, CA 91201 (818) 332-5214 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
