All oDesk Jobs » Search Results » Job Facts
 oDesk Verified
Payment Method
Flag as inappropriate
optimize a mysql query
Open
Date Posted:November 7, 2009
Planned Start Date:November 7, 2009
Type:Hourly
Main Category:Web Development
Sub Category:Other - Web Development
Skills: MYSQL4 optimization
Estimated Workload:As needed - Less than 10 hrs/week
Estimated Duration:Less than 1 week
Last Buyer Activity:November 10, 2009
Candidates: 5 - average $14.51/hr
Interviews: none
 
In Progress
Start Date:November 8, 2009
Hourly Rate:$25.00
Last Date Worked:none
Hired Provider: Sergey Grachyov
Hours Worked:0
Offline Hours:0%
Bonuses: none
Refunds: none
Completed
End Date:November 10, 2009
Feedback to Provider: none
Feedback to Buyer: none
 
Buyer Facts
Member Since:January 14, 2007
Country:United States (GMT-05)
City:Northborough
Jobs Posted:9
Jobs Filled:5
Jobs Not Yet Filled:1
Current Team size:1
Hours billed, last 30 days:0
Total oDesk Hours:9.83
Feedback Score: 5.00 (3)
 

I have a simple mysql query that only involves one table - it runs too slowly and I'd like to know how to speed it up.

here's the query
select  *  from shows  where  ( lat BETWEEN '39.364477419355' and '41.945122580645' )  and ( lon BETWEEN '-75.239222580645' and '-72.658577419355' ) and  ( whenint BETWEEN '1257532285' and  '1267900285')      order by whenint asc  limit 300

EXPLAIN shows it doing a Simple select and using an index "using Where, using Filesort"

When I do Show Processlist I see it Sorting Results a lot - Im suspecting it's not using the index on whenint to sort.

There is an index on whenint and one on [lat,lon,whenint,visitcount] 

Query often runs longer than 3 seconds and I need to get it to under .5 second.

Buyer's Assignment History
Provider From/To Job Title Billed Feedback
304919 11/2009 - Present upgrade widget to googlemaps Flash API $4 (0.17 hrs @ $25.00/hr)
304919 10/2009 - 11/2009 upgrade widget to googlemaps Flash API $184 (fixed-price)
49578 08/2008 - 02/2009 PHP4 functions to wrap SOAP calls $35 (fixed-price)
116533 06/2008 - 06/2008 PHP function to Parse XML file $28 (fixed-price)
23262 08/2007 - 12/2007 embed gmap in flash widget $121 (9.67 hrs @ $12.50/hr)
23262 08/2007 - 08/2007 embed gmap in flash widget $56 (fixed-price)

Candidate List
Title Name Initiated By Date
6 years experienced ASP/ASP.NET/Silverlight/RIA & AJAX developer. Vijay G. Provider November 7, 2009
PHP,mysql, Ajax,JS,oscommerce, Joomla, zen cart,drupal,magento Prem S. Provider November 7, 2009
Joomla/drupal/wordpress/Php/MySql/Ajax/Opencart Developer Mayank Kumar Provider November 7, 2009
Experienced Dev (MCDBA, MCAD.NET, 1st in the "SQL Server 2005" test) Sergey Grachyov Provider November 7, 2009
Finest Outsourcing Firm in India Mohit Aggarwal Provider November 8, 2009