Thanks to all that replied, it turns out that the great delay was due to the use of the "like" opperator, I did have an index on person_id + session_start, just a thought though, using this will mysql pull out data ordered as per the index?
Yes, sql is VERY limited, thanks for the pointers. Merry Christmas. Rob Keeling "Donny Simonton" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Rob, > First of all I would say, your query is pretty badly laid out. First, > unless you need every fields from a table returned only ask for those > specific fields, and do you have an index on the combination of person_id + > session_start? If not, your query will always be slow. > > But this is how I would write it. > > Select bla, bla2 from table > where person_id = 10 > and session_start between ('2004-09-01 00:00:00' and '2004-10-01 00:00:00') > > Then why would you order by session_start, when the odds are that you added > the data to the table by time anyway. So why waste the servers time > ordering something that may already be ordered for you automatically. But > you would know that better than any of us. > > A query like this should take no longer than 0.1 seconds to execute in most > cases, even with a few gigs of data. > > Doonny > > > -----Original Message----- > > From: news [mailto:[EMAIL PROTECTED] On Behalf Of Rob Keeling > > Sent: Thursday, December 23, 2004 11:55 AM > > To: mysql@lists.mysql.com > > Subject: Optimising a query on a large table. > > > > I have a 152MB MyISAM table that I am trying to execute a simple select > > statement on, > > I need to retreave all rows with a given index, sorted by date. > > > > This is taking a very long period of time to execute. > > > > What can I do to speed up the query. > > > > The sql is, > > > > SELECT * FROM table WHERE (person_id LIKE 10) AND (session_start > > > '2004-09-01 00:00:00') AND (session_start < '2004-10-01 00:00:00') Order > > by > > session_start > > > > Thanks > > > > Rob Keeling > > > > > > - > > -- > > > > I love deadlines. I love the whooshing noise they make as they go by. > > - Douglas Adams > > > > > > > > > > -- > > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
