...

View Full Version : slow query



BubikolRamios
09-19-2011, 11:41 AM
Managed to do a wery slow query,
this takes like 4 - 5 sec on fast machine, on tables with like 3000/200 records.
It does not use any indexes, I gues I cant make index on RIGHT(c.jsp,32) ?



select c.dsp, c.comment from comments c
join galery_1 g on RIGHT(c.jsp,32) = g.id_galery_obfuscated
order by c.dsp desc LIMIT 0,5

Any suggestions, besides inventing new keyed column ?

reforminfotech
09-19-2011, 01:52 PM
SELECT Comments.* from Comments INNER JOIN galery_1 ON RIGHT(Comments.jsp,32) = galery_1.id_galery_obfuscated order by Comments.dsp desc LIMIT 0,5

Note: that this query will work only in MY SQL server and not on MS SQL server
as LIMIT is not supported in MS SQL

-------------------------
Mobile Application Development (http://www.reforminfotech.com/services/mobile-application-development.aspx)

Old Pedant
09-19-2011, 11:19 PM
I give up. What did "reforminfotech" change in that query that could possibly matter to performance?

I think maybe he doesn't understand that Bubikol was simply using table aliases to shorten his coding??

Old Pedant
09-19-2011, 11:23 PM
Bubikol:



ALTER TABLE comments ADD jsp32 VARCHAR(32);

UPDATE comments SET jsp32 = RIGHT(jsp,32);

CREATE INDEX comments_jsp32 ON coments(jsp32);


*NOW* do


select c.dsp, c.comment
from comments c join galery_1 g
on c.jsp32 = g.id_galery_obfuscated
order by c.dsp desc LIMIT 0,5


In other words, if you will do this all the time, add an indexed field specifically for this purpose.

If need be, add a TRIGGER to maintain the added jsp32 field whenever jsp field is updated.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum