Solved

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

Posted on 2014-10-06
4
321 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 108

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 108

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
The viewer will learn how to dynamically set the form action using jQuery.
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.

708 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

15 Experts available now in Live!

Get 1:1 Help Now