My first query was: $sql = 'SELECT count(*) as cnt,id FROM `myTable` WHERE c1<>1 and c2<>1 and c3<>1 and c4<>1 and c5<>1 and c1<>16 and c2<>16 and c3<>16 and c4<>16 and c5<>16 and c1<>36 and c2<>36 and c3<>36 and c4<>36 and c5<>36 GROUP BY id';
By using the "IN" statement, it reduced my query time by 24%, and is why I was inquiring about my second option. There are only 6 columns, all TinyInt. Indexing any single, multiple or all columns had no noticeable change in querys execution time. FYI, there are some 3 million records. -----Original Message----- From: Marek Kilimajer [mailto:[EMAIL PROTECTED] Sent: Saturday, June 05, 2004 12:56 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [PHP] DB Query bskolb wrote: > I'm trying to optimize a query that in the first example is taking too > long to run. > > This is my existing working query: > $sql = "SELECT count(*) as cnt, id FROM `mYTable` WHERE c1 not in > (1,16,36) and c2 not in (1,16,36) and c3 not in (1,16,36) and c4 not > in (1,16,36) and > c5 not in (1,16,36) GROUP BY id"; > > Is it possible to do something like this instead (This doesn't work of > course) > $sql = "SELECT count(*) as cnt, id FROM `myTable` WHERE > (c1,c2,c3,c4,c5) not in (1,16,36) GROUP BY id"; > > Any other suggestions are appreciated, > > Thanks! > > > Even if the syntax whould be possible, it whould not help any. SQL server optimize queries before executing them, and your second query would be the same as the first one. What is the column type of c1,c2,c3,c4 and c5? What indexes did you create? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
