+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Database query question (/showthread.php?tid=20178)
Database query question - El Forum - 07-01-2009
[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 20On 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?
Database query question - El Forum - 07-01-2009
[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
Database query question - El Forum - 07-01-2009
[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?
Database query question - El Forum - 07-01-2009
[eluser]emperius[/eluser]
I'm using mysql on hosting server. There is no profiler.
Database query question - El Forum - 07-01-2009
[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
Database query question - El Forum - 07-01-2009
[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
Database query question - El Forum - 07-01-2009
[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?
Database query question - El Forum - 07-01-2009
[eluser]emperius[/eluser]
About 30s
Database query question - El Forum - 07-01-2009
[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?
Database query question - El Forum - 07-01-2009
[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?