Mysql – How to speed up this simple thesql query

MySQL

The query is simple:

SELECT
  TB.ID,
  TB.Latitude,
  TB.Longitude,
  111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*cos(-6.185*0.017453292519943)*cos(TB.Latitude*0.017453292519943)) AS Distance
FROM
  `tablebusiness` AS TB
WHERE
  -6.2767668133836 < TB.Latitude AND TB.Latitude < -6.0932331866164
AND FoursquarePeopleCount >5   AND 106.68123318662 < TB.Longitude AND TB.Longitude <106.86476681338
ORDER BY
  Distance

See, we just look at all business within a rectangle. 1.6 million rows. Within that small rectangle there are only 67,565 businesses.

The structure of the table is

1   ID  varchar(250)    utf8_unicode_ci         No  None        Change Change   Drop Drop   More Show more actions
2   Email   varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
3   InBuildingAddress   varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
4   Price   int(10)             Yes     NULL        Change Change   Drop Drop   More Show more actions
5   Street  varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
6   Title   varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
7   Website     varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
8   Zip     varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
9   Rating Star     double          Yes     NULL        Change Change   Drop Drop   More Show more actions
10  Rating Weight   double          Yes     NULL        Change Change   Drop Drop   More Show more actions
11  Latitude    double          Yes     NULL        Change Change   Drop Drop   More Show more actions
12  Longitude   double          Yes     NULL        Change Change   Drop Drop   More Show more actions
13  Building    varchar(200)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
14  City    varchar(100)    utf8_unicode_ci         No  None        Change Change   Drop Drop   More Show more actions
15  OpeningHour     varchar(400)    utf8_unicode_ci         Yes     NULL        Change Change   Drop Drop   More Show more actions
16  TimeStamp   timestamp       on update CURRENT_TIMESTAMP     No  CURRENT_TIMESTAMP   ON UPDATE CURRENT_TIMESTAMP     Change Change   Drop Drop   More Show more actions
17  CountViews  int(11)             Yes     NULL        Change Change   Drop Drop   More Show more actions

The indexes are:

Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  ID  1965990 A       
Edit Edit   Drop Drop   City    BTREE   No  No  City    131066  A       
Edit Edit   Drop Drop   Building    BTREE   No  No  Building    21  A   YES 
Edit Edit   Drop Drop   OpeningHour BTREE   No  No  OpeningHour (255)   21  A   YES 
Edit Edit   Drop Drop   Email   BTREE   No  No  Email (255) 21  A   YES 
Edit Edit   Drop Drop   InBuildingAddress   BTREE   No  No  InBuildingAddress (255) 21  A   YES 
Edit Edit   Drop Drop   Price   BTREE   No  No  Price   21  A   YES 
Edit Edit   Drop Drop   Street  BTREE   No  No  Street (255)    982995  A   YES 
Edit Edit   Drop Drop   Title   BTREE   No  No  Title (255) 1965990 A   YES 
Edit Edit   Drop Drop   Website BTREE   No  No  Website (255)   491497  A   YES 
Edit Edit   Drop Drop   Zip BTREE   No  No  Zip (255)   178726  A   YES 
Edit Edit   Drop Drop   Rating Star BTREE   No  No  Rating Star 21  A   YES 
Edit Edit   Drop Drop   Rating Weight   BTREE   No  No  Rating Weight   21  A   YES 
Edit Edit   Drop Drop   Latitude    BTREE   No  No  Latitude    1965990 A   YES 
Edit Edit   Drop Drop   Longitude   BTREE   No  No  Longitude   1965990 A   YES

The query took forever. I think there has to be something wrong there.

Showing rows 0 – 29 ( 67,565 total, Query took 12.4767 sec)

Explain SQL shows this:

1   SIMPLE  TB  ALL     Latitude,Longitude  NULL    NULL    NULL    1642681     Using where; Using filesort

Update: Not computing distance takes only .0113 seconds! I do not "really" need the distance. I only need things like 20 or 100 closest match.

MySQL does not contain GEO feature I think. Or maybe it's what I am missing. I host at hostgator. The most famous (and best) hosting company in the world.

Best Answer

I've only given this a little thought, it seems like you are scanning the whole table (which involves reading much extraneous information). I'd try creating a separate geo table containing the LAT/Long info and precalculate the Distance expression on insertion, index the Distance column, do your search on the geo table and reference the main table only when you need information from it.

Related Solutions

MySql – How to speed up this query

Here is your original query:

SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`  
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

First thing I notice is that you are joining two whole tables. Since you only need twitter_username and email from the users table, you should only join from users using three columns : id, twitter_username and email.

Second thing is the LIMIT clause. It is being executed after the join. You should execute it before the join. In your case, you are requesting for the 30 most recent user actions. If you can guarantee that only 30 rows are retreived from user_actions, the join should operate much faster.

If you read the answer from @DTest, his first two bulletpoints already tell you what's wrong the query because of the actions mysql will take in gathering data from each table. The key is to understand what the temp tables will look like while the query is being processed and where the data will reside (memory or disk).

What you need to do is refactor the query to fool the MySQL Query Optimizer. Force the query to produce smaller temp tables. In most cases, config changes in my.cnf should make a dramamtic difference. In other cases, such as this one, refactoring the query may be sufficient.

Here is my proposed change to your query that should work faster:

SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Here are reasons for refactoring the query:

REASON #1

If you look at inline table ua, I retrieve only 30 rows using LIMIT. This will happen no matter how big the user_actions table gets. It is already ordered because the ORDER BY timestamp DESC happens before the LIMIT.

REASON #2

If you look inline table u, it has id,twitter_username,email. The id is needed to implement the join.

REASON #3

I use LEFT JOIN instead of INNER JOIN for two(2) reasons:

Preserve the order of the query based on ua
Display all user actions in case the user_id in the ua no longer exists in the users tables.

Doing these things will force the temp tables to be smaller. Nevertheless, you will still need implement bulletpoint #3 from @DTest's answer to preempt having temp tables land on disk.

MySQL optimization question — how to speed up a simple query

Here is your original query

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE t.ticker='LAV05 Comdty'
AND t.whichprice='last'
t.date = '2005-09-01';

Since there is but one table in this query, all you need is a good index.

ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date);

This index should now be a permanent part of the table until you decide to remove it, which I don't think you want to do. To see the table and the indexes, just do this:

SHOW CREATE TABLE commoddb.tbcommodprices\G