Welcome Guest, Not a member yet? Register   Sign In
Database query question
#1

[eluser]emperius[/eluser]
I have the following query:
Code:
SELECT
    Object.ID,
    Object.NasPunkt,
    Object.Area,
    Object.Distance,
    District.Value AS District,
    (SELECT Photo FROM ObjectPhoto WHERE ObjectID = Object.ID ORDER BY PhotoOrder ASC, ID DESC LIMIT 1) AS Photo
FROM
    Object
LEFT OUTER JOIN
    District
        ON District.ID = Object.DistrictID
WHERE
Object.Special=1                                                
ORDER BY Object.ID DESC
LIMIT 20

On page loading it takes a long time, even when there are many visitors there's 503 error. In my opinion there's nothing that may take so long time for page loading.

How do you think what may be the problem?
#2

[eluser]Dam1an[/eluser]
Have you tried using the profiler?
It will give you an idea how how much of the total time the SQL query takes, so you'll know if thats the root of the problem.

You can also use the Benchmarking class to make your own timings between various points, allowing you to firther see where you spend the most time
#3

[eluser]jedd[/eluser]
How do you know it's this query that is slow?

How long does it take when you bang that into the CLI?
#4

[eluser]emperius[/eluser]
I'm using mysql on hosting server. There is no profiler.
#5

[eluser]emperius[/eluser]
[quote author="jedd" date="1246456885"]How do you know it's this query that is slow?

How long does it take when you bang that into the CLI?[/quote]

Because when I'm removing block with this query everything works fast
#6

[eluser]Dam1an[/eluser]
[quote author="emperius" date="1246457348"]I'm using mysql on hosting server. There is no profiler.[/quote]

I'm refering to the CI profiler, hence linking to it to avoid any confusion
#7

[eluser]jedd[/eluser]
[quote author="emperius" date="1246457457"][quote author="jedd" date="1246456885"]How do you know it's this query that is slow?

How long does it take when you bang that into the CLI?[/quote]

Because when I'm removing block with this query everything works fast[/quote]

How long does it take when you bang that into the CLI?
#8

[eluser]emperius[/eluser]
About 30s
#9

[eluser]jedd[/eluser]
[quote author="emperius" date="1246457898"]About 30s[/quote]

Gordon Bennet.

Okay. Warm, comfortable place.

How long does your sub-SELECT take?

How long does it take if you remove the ORDER BY?

How long does it take if you remove the OUTER JOIN?

What have you learned from using the EXPLAIN feature?

In what way is this related to CI at all?
#10

[eluser]Evil Wizard[/eluser]
you do have indexes on those tables don't you?

field

ObjectID in ObjectPhoto
Special in Object
DistrictID in Object

I assume the ID fields will be primary keys and therefore already indexes.
Have a look at the explain of the query to see how many records it searches through, and have you thought about converting the sub select query into a join?




Theme © iAndrew 2016 - Forum software by © MyBB