search

SPATIAL-Lab / spatial-group-demo

0 stars 0 forks source link

single_site.php query speed #7

Open bumbanian opened 7 years ago

bumbanian commented 7 years ago

Some queries are taking >3 seconds to complete. Explore strategies for improving query time, including indexing.

HuanUU commented 7 years ago

We can try the indexing method.

  1. Add a new column named "id" in all tables (Sites, Samples, Projects, Water_Isotope_Data), and set it to integer , primary key, and AI.
  2. Change the info of "Site_ID" (may called "Sites_id") in Samples table to the number of "id" in the Sites table. Change the info of "Project_ID" (may called "Projects_id") in Samples table to the number of "id" in the Projects table.
  3. Change the info of "Sample_ID" (may called "Samples_id") in Water_Isotope_Data table to number of "id" in the Samples table.

Thanks, Chonghuan

bumbanian commented 7 years ago

Thanks…I think this is too much of a change at this point given that we have a number of different scripts and people working w/ the DB actively. I ran a bunch of single site queries today and was very consistently getting ~1.7 second wall times for simple sites (w/ only a few data) up to 2.8 seconds for some of the most data-rich sites. Not sure if anything has changed w/ the DB but this is better than what we were seeing before and seems acceptable to me.

Gabe

From: HuanUU [mailto:notifications@github.com] Sent: Tuesday, March 28, 2017 10:44 AM To: SPATIAL-Lab/spatial-group-demo Cc: GABRIEL J BOWEN; Author Subject: Re: [SPATIAL-Lab/spatial-group-demo] single_site.php query speed (#7)

We can try the indexing method.

  1. Add a new column named "id" in all tables (Sites, Samples, Projects, Water_Isotope_Data), and set it to integer , primary key, and AI.
  2. Change the info of "Site_ID" (may called "Sites_id") in Samples table to the number of "id" in the Sites table. Change the info of "Project_ID" (may called "Projects_id") in Samples table to the number of "id" in the Projects table.
  3. Change the info of "Sample_ID" (may called "Samples_id") in Water_Isotope_Data table to number of "id" in the Samples table.

Thanks, Chonghuan

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/SPATIAL-Lab/spatial-group-demo/issues/7#issuecomment-289831379, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AIy0N1nJbwCEThOUXUqin9xw-fY7A4Gsks5rqTjtgaJpZM4MXBwH.