Dear mysql-ians,
I am performing a query that takes ages before showing a result. I
suppose it is very time consuming because of the JOIN I perform. My
question therefore is if you have any suggestions to optimize my query?
What I want to do is divide a value of a cell (NDVI) by the median of
its neighbours based on geographical coordinates (refgeo).
The selection script is as follows:
SELECT
NDVI.id,
NDVI.NDVI_99_24 *IF(B.B_99_24<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_24*IF(r efB.B_99_24<30, 1,NULL)),
NDVI.NDVI_99_25 *IF(B.B_99_25<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_25*IF(r efB.B_99_25<30, 1,NULL)),
NDVI.NDVI_99_26 *IF(B.B_99_26<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_26*IF(r efB.B_99_26<30, 1,NULL)),
....,
NDVI.NDVI_99_17 0*IF(B.B_99_170 <30,1,NULL)/MEDIAN(refNDVI. NDVI_99_170*IF( refB.B_99_170<3 0,1,NULL))
FROM vgt.NDVI AS NDVI
INNER JOIN vgt.B AS B
USING (id)
INNER JOIN vgt.v_ecoclim AS eco
USING (id)
INNER JOIN vgt.v_vcf2001 AS vcf
USING (id)
,
(vgt.meta_gsc AS refmgsc
INNER JOIN vgt.gsc AS refgsc
ON refgsc.gsc_XX = refmgsc.id)
INNER JOIN vgt.geo AS refgeo
ON refgeo.id = refgsc.id
INNER JOIN vgt.B AS refB
ON refgeo.id = refB.id
INNER JOIN vgt.v_ecoclim AS refeco
ON refgeo.id = refeco.id
INNER JOIN vgt.v_vcf2001 AS refvcf
ON refgeo.id = refvcf.id
INNER JOIN (vgt.gba AS refgba
INNER JOIN vgt.meta_gba AS refmgba
ON refgba.gba_XX = refmgba.id)
ON refgeo.id=refgb a.id
INNER JOIN vgt.NDVI AS refNDVI
ON refgeo.id = refNDVI.id
WHERE NDVI.id= 1133568
AND refgeo.X_coord > 1288
AND refgeo.X_coord < 1328
AND refgeo.Y_coord > 659
AND refgeo.Y_coord < 699
AND refmgba.burnt=0
AND refmgsc.burnt=0
AND refeco.v_landco v=eco.v_landcov
AND refeco.v_lowreb =eco.v_lowreb
AND refvcf.vcf_bare < vcf.vcf_bare+20
AND refvcf.vcf_bare > vcf.vcf_bare-20
AND refvcf.vcf_tree < vcf.vcf_tree+20
AND refvcf.vcf_tree > vcf.vcf_tree-20
AND refvcf.vcf_herb < vcf.vcf_herb+20
AND refvcf.vcf_herb > vcf.vcf_herb-20
GROUP BY refmgba.burnt
I have thus several main-tables that have identical rows (and are
linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a
manyto1-relationship. I have put it in a scheme below (view using
Courier font for correct spacing).
The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba,
refB, refvcf, refNDVI.
The 2 subtables: refmgba, refmgsc.
Is my query correct? Do you have any solutions to make my query more
efficient?
I've studied the mysql-manual, but I don't see answers on the moment
Thanks in advance,
Stef
Schematic overview tables:
[=============] [=============] [============]
[T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O
[=============] [=============] [============] T
[ ID ]<--->[ ID ]<->[ ID ]<->H
|-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E
| [ Others... ] | [ Others... ] [ NDVI_99_25 ] R
| [=============] | [=============] [ Others... ] S
| | [============]
| |
| |
| [=============] | [=============]
| [T: refmgsc ] | [T: refmgba ]
| [=============] | [=============]
|--> [ ID(1) ] |--> [ ID(1) ]
[ BURNT ] [ BURNT ]
[ Others... ] [ Others... ]
[=============] [=============] 1 1792 st************* **@agr.kuleuven .ac.be wrote: Dear mysql-ians,
I am performing a query that takes ages before showing a result. I suppose it is very time consuming because of the JOIN I perform. My question therefore is if you have any suggestions to optimize my query?
What I want to do is divide a value of a cell (NDVI) by the median of its neighbours based on geographical coordinates (refgeo). The selection script is as follows:
SELECT NDVI.id,
NDVI.NDVI_99_24 *IF(B.B_99_24<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_24*IF(r efB.B_99_24<30, 1,NULL)),
NDVI.NDVI_99_25 *IF(B.B_99_25<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_25*IF(r efB.B_99_25<30, 1,NULL)),
NDVI.NDVI_99_26 *IF(B.B_99_26<3 0,1,NULL)/MEDIAN(refNDVI. NDVI_99_26*IF(r efB.B_99_26<30, 1,NULL)), ....,
NDVI.NDVI_99_17 0*IF(B.B_99_170 <30,1,NULL)/MEDIAN(refNDVI. NDVI_99_170*IF( refB.B_99_170<3 0,1,NULL))
FROM vgt.NDVI AS NDVI INNER JOIN vgt.B AS B USING (id) INNER JOIN vgt.v_ecoclim AS eco USING (id) INNER JOIN vgt.v_vcf2001 AS vcf USING (id) , (vgt.meta_gsc AS refmgsc INNER JOIN vgt.gsc AS refgsc ON refgsc.gsc_XX = refmgsc.id) INNER JOIN vgt.geo AS refgeo ON refgeo.id = refgsc.id INNER JOIN vgt.B AS refB ON refgeo.id = refB.id INNER JOIN vgt.v_ecoclim AS refeco ON refgeo.id = refeco.id INNER JOIN vgt.v_vcf2001 AS refvcf ON refgeo.id = refvcf.id INNER JOIN (vgt.gba AS refgba INNER JOIN vgt.meta_gba AS refmgba ON refgba.gba_XX = refmgba.id) ON refgeo.id=refgb a.id INNER JOIN vgt.NDVI AS refNDVI ON refgeo.id = refNDVI.id WHERE NDVI.id= 1133568 AND refgeo.X_coord > 1288 AND refgeo.X_coord < 1328 AND refgeo.Y_coord > 659 AND refgeo.Y_coord < 699 AND refmgba.burnt=0 AND refmgsc.burnt=0 AND refeco.v_landco v=eco.v_landcov AND refeco.v_lowreb =eco.v_lowreb AND refvcf.vcf_bare < vcf.vcf_bare+20 AND refvcf.vcf_bare > vcf.vcf_bare-20 AND refvcf.vcf_tree < vcf.vcf_tree+20 AND refvcf.vcf_tree > vcf.vcf_tree-20 AND refvcf.vcf_herb < vcf.vcf_herb+20 AND refvcf.vcf_herb > vcf.vcf_herb-20 GROUP BY refmgba.burnt
I have thus several main-tables that have identical rows (and are linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a manyto1-relationship. I have put it in a scheme below (view using Courier font for correct spacing).
The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba, refB, refvcf, refNDVI. The 2 subtables: refmgba, refmgsc.
Is my query correct? Do you have any solutions to make my query more efficient? I've studied the mysql-manual, but I don't see answers on the moment
Thanks in advance, Stef
Schematic overview tables:
[=============] [=============] [============] [T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O [=============] [=============] [============] T [ ID ]<--->[ ID ]<->[ ID ]<->H |-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E | [ Others... ] | [ Others... ] [ NDVI_99_25 ] R | [=============] | [=============] [ Others... ] S | | [============] | | | | | [=============] | [=============] | [T: refmgsc ] | [T: refmgba ] | [=============] | [=============] |--> [ ID(1) ] |--> [ ID(1) ] [ BURNT ] [ BURNT ] [ Others... ] [ Others... ] [=============] [=============]
make sure you have indexes that support your where-clause. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: ArShAm |
last post by:
Hi there
Please help me to optimize this code for speed
I added /O2 to compiler settings
I added /Oe to compiler settings for accepting register type request , but
it seems that is not allowed and if I remove register type for "l" , time of
generating codes doesn't change
the original code makes some files , but I removed that section to make it
simple for you to read
please help me to optimize it for faster running
|
by: Soefara |
last post by:
Dear Sirs,
I am experiencing strange results when trying to optimize a LEFT JOIN
on 3 tables using MySQL.
Given 3 tables A, B, C such as the following:
create table A (
uniqueId int not null default 0 auto_increment,
a1 varchar(64) not null default '',
|
by: Ron Johnson |
last post by:
On Mon, 2003-08-25 at 16:28, Gregory S. Williamson wrote:
> One of our sysads sent this link ... wondering if there is any comment on it from the world of actual users of linux and a database.
>
> <http://story.news.yahoo.com/news?tmpl=story&cid=1738&ncid=738&e=9&u=/zd/20030825/tc_zd/55311>
"Weak points include lack of available tools, ease of use and ease
of installation"
Sounds like he needs point-and-drool tools...
|
by: Vendell |
last post by:
Join one of Canada's finest business men...
Dear entrepreneur colleague:
Here is a message from the founder....
Let me introduce myself. My name is Ariel Topf. I am 42 years old and I have
been retired now for a few years and I feel like it's time to do something
again.
|
by: frizzle |
last post by:
Hi there,
I'm building a forum with a mysql backend. The forum has the following
structure:
- category
|--> forum
|--> thread
|--> replies
Once the user has defined a forum (id), i first need to get the name of
| |
by: Richard G. Riley |
last post by:
Without resorting to asm chunks I'm working on a few small routines
which manipulate bitmasks. I'm looking for any guidance on writing C
in a manner which tilts the compilers hand in, if possible, a
compiler/underlying processor independant way : althought to be fair I
cant see this stuff on anything other than x86, but who knows.
I found some ok info here:
http://www.eventhelix.com/RealtimeMantra/Basics/OptimizingCAndCPPCode.htm...
|
by: mamapossible |
last post by:
Hi, I've spent hours trying to optimize this simple query:
SELECT count(sites_jobs.id) as jobCount,
sites_jobs_categories.frn_site_id, sites_jobs_categories.id,
sites_jobs_categories.name,
FROM sites_jobs_categories
LEFT JOIN sites_jobs ON sites_jobs.frn_jobs_categories_id =
sites_jobs_categories.id
GROUP BY sites_jobs_categories.id
HAVING sites_jobs_categories.frn_site_id = #request.siteId#
|
by: Mark E. Fenner |
last post by:
Hello all,
I have a code where my inner loop looks like:
allNew =
for params in cases:
newObj = copy(initialObject)
newObj.modify(params)
allNew.append(newObj)
return allNew
|
by: erbrose |
last post by:
Hello All!
Hoping some folks could help me optimize and or choose the best route to do this process.
First off, here is what I am trying to achieve. I have a (fairly large) table of ~34million rows that looks something like this.
ID,TIME,SPD
1,1,35
1,2,25
1,3,34
1,4,25
2,1,25
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |