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

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!
brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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
Ray PaseurCommented:
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
Beverley PortlockCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brucegustPHP DeveloperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.