Solved

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

Posted on 2014-10-06
4
328 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 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 150 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 109

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 350 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
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.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now