NYCPHP Meetup

[Patrick Ramsden]

For that first query, I think you want MySql to use the index on SID because
it limits the result set -- otherwise it would have to do a table scan.  

I'm not sure how MySql handles multiple index elements but perhaps having an
combined index on SID and ID could help.

HTH, Pat

> --- Amir Aavani <amir.aavani at gmail.com> wrote:
> Dear Leila,
> I misstyped the query, (count(*) must be replaced by * ),
> 
> On 7/28/05, Leila Lappin <damovand at yahoo.com> wrote:
> > I don't know if this has been answered or not but,
> > this select statement simply returns the number of
> > records in the table.  Since you're not returning 'id'
> > therefore nothing happens to the order.
> > 
> > --- Amir Aavani <amir.aavani at gmail.com> wrote:
> > 
> > > Dear friends
> > > I have some problem in getting query from mysql,
> > > I have table named news in following fornat
> > > ID Int, sid Int, Title Varchar , .... data DateTime
> > > I have a query like this
> > > SELECT count(*) from news where sid in (1, 2, 3, 4,
> > > 5 ,6, ...) order
> > > by id LIMIT 0, 10;
> > > I want to force mysql to use id as index (not sid)
> > > (because of big
> > > size of my table) but I was unsuccessfull in that,
> > > and always mysql
> > > use filesort to sort the results.
> > > Something wonderfull is when I changed the query to
> > > SELECT count(*) from news where (id< 1000 or id>
> > > 100000) order by id
> > > LIMIT 0, 10;
> > > mysql didn't use id as index.