?
Solved

I need to add some indexes to my mysql table...

Posted on 2014-10-06
4
Medium Priority
?
337 Views
Last Modified: 2014-10-06
Here's my columns:

id [int(11)]
twitter_id [varchar(150)]
actor_id [varchar(150)]
actor_display_name [varchar(150)]
poted_time [datetime]
display_name [varchar(150)]
geo_coorss_lat [decimal (16,9)]
geo_coords_lon [decimal (16,9)]
location_name [carchar(150)]
posted_day [date]

The table contains over 250,000,000 rows so indexes are going to be needed in order to facilitate a query that can happen in a reasonable amount of time.

I'm looking at some of the documentation that's out there and it seems like there's more to this than just slapping an index on a table and expecting everything to hum (http://hackmysql.com/case1). So, given that epiphany, here's the one and only query that's going to be executed against this table:

 $don="SELECT * FROM Verizon WHERE (geo_coords_0>=$latitude_1 and geo_coords_0<=$latitude_2) and (geo_coords_1>=$longitude_1 and geo_coords_1<=$longitude_2) and
(posted_day>='$start_date' and posted_day<='$end_date')";
$don_query=mysqli_query($cxn, $don);
      if(!$don_query)
      {
      $nuts=mysqli_errno($cxn).': '.mysqli_error($cxn);
      die($nuts);
      }

I'm thinking I need an index on "posted_day," "geo_coords_lat" and "geo_coords_lon," but there's an order that I need to be sensitive to and I'm not sure how I add an index on "posted_day" in that my options appear to be either a string or an integer.

Bottom line: How do I add these indexes to my table so I'm doing it right the first time?

Thanks!
0
Comment
Question by:brucegust
  • 2
4 Comments
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 600 total points
ID: 40364016
Change SELECT * to name the columns you want to SELECT.  Never write SELECT * in a deployed application if it is at all possible to avoid it.  

Use ALTER TABLE to add indexes.  You want indexes on all columns used in WHERE, GROUP, JOIN, ORDER, HAVING, etc., -- any column that is used my MySQL in a "meta" context during the processing of the query.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40364026
Also, since it is a 250 million row table, consider getting a DBA involved to help you design the application.  It would not cost you very much money and it might save you a ton of headaches later!
0
 
LVL 34

Accepted Solution

by:
Beverley Portlock earned 1400 total points
ID: 40364450
I agree with Ray about the SELECT * - you should only bring back the fields you need, especially if the table has a large number of columns.

With regard to your indexes, try a simple solution first and just add indexes on the three columns you mention in the query like so

ALTER TABLE Verizon ADD INDEX ( posted_day );

ALTER TABLE Verizon ADD INDEX ( geo_coords_0 );

ALTER TABLE Verizon ADD INDEX ( geo_coords_1 );

Open in new window


Whilst compound indexes and other such structures can be useful, in this case I would not expect it to be a lot of use because of the coordinates being DOUBLE. This allows an enormous set of mixed day/lat/long combinations.
0
 

Author Comment

by:brucegust
ID: 40364596
That will do it, Beverley! Thanks!

BTW: If you're so inclined, I've got another question: http://www.experts-exchange.com/Database/MySQL/Q_28532337.html
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question