?
Solved

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

Posted on 2014-10-06
4
Medium Priority
?
335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

WordPress Tutorial 2: Terminology

An important part of learning any new piece of software is understanding the terminology it uses. Thankfully WordPress uses fairly simple names for everything that make it easy to start using the software.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

801 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