[RESOLVED] "Speeding up" quiery

zzz

Hi,

I am attempting to write a query that would display users based on a set of criteria, but because I have to access all these params the display of the results has quite a drag. I think I need someone to take a look at what I have and let me doing if I'm overcomplicating it. Here's my monster:

$sql = ("SELECT
			t1.name_f,
			t1.name_l,
			t2.altname_f,
			t2.altname_l,
			t3.gender
	     FROM t1
	     LEFT JOIN t2 ON (t2.id = t1.id)
	     LEFT JOIN t3 ON (t3.id = t1.id)
	     LEFT JOIN t4 ON (t4.id = t1.id)
	     WHERE t1.status = 1
	     AND t4.product = 2
	     AND t3.gender != ''
	     ORDER BY 
	   		t2.altname_l,
			t2.altname_f,
			t1.name_l,
			t1.name_f
	     DESC ");

Piranha

First thing is of course to have proper indexes set, it will speed up the query a lot. But since you have posted here lots of times before I guess that you have proper indexes already.

It seems like you use "LEFT JOIN" without thinking about it. I guess that to join t3 and t4 it would be better with an "INNER JOIN" since it will create fewer rows. Since you compare columns from those two tables you don't need the empty rows.

Do you use all the results? If not you might want to only use the rows you need with LIMIT.

By ordering both ASC and DESC the indexes won't be used in mysql. Is it possible that you don't need to order by t1.name_f?

zzz

Thanks. The reason I posted here before is because I have no formal training as a developer, but rather learn as I go. How do i make sure my indexes are set properly? I use MyPHPAdmin and I do not have any warnings or errors...

I'll try INNER JOIN and see if it helps. I used LEFT JOIN because I needed to make sure the conditions are met. I'm still foggy on this whole join issue...

I need all results but I do use limit if the number of total results exceed, say 20, so I can do result paging.

The reason I do the ORDER by in such odd way because not all values are provided. Ideally I need to sort by t2.altname_l -- if provided, if not by t2.altname_f -- if provided, if not by t1.name_l and so on.

Not sure what do you mean by: "By ordering both ASC and DESC the indexes won't be used in mysql"

Thanks for the great suggestions. I'll give it a whirl right away.

zzz

I just fixed it!

First I changed my primary table, istead of FROM t1 to FROM t3. I totally neglected the fact that if I do that, I don't need to even invoke t4... Dah...
Also I changed to INNER JOIN. It works lightning fast now. I am very impressed. Same result output but what an improvement!!! Wow!

Piranha

zzz wrote:
Not sure what do you mean by: "By ordering both ASC and DESC the indexes won't be used in mysql"

You have ordered like this:

ORDER BY
            t2.altname_l,
            t2.altname_f,
            t1.name_l,
            t1.name_f DESC

That is the same as below:

ORDER BY
            t2.altname_l ASC,
            t2.altname_f ASC,
            t1.name_l ASC,
            t1.name_f DESC

If you do not state in what order to order it will order ASC.

Normally when you order it uses the indexes to order the rows. If you for example "order by t1.fname ASC, t1.lname ASC, t1.state ASC, t1.city ASC, t1.street ASC" and you have all those columns in the index it will use the index to index. That means that it doesn't have to load the actual content of the table, compare that and index from it. But if you mix ASC and DESC "order by t1.fname ASC, t1.lname ASC, t1.state ASC, t1.city ASC, t1.street DESC" it can't use the indexes, it have to load the actual data instead.

About making sure that indexes are set properly I recommend you to search and read about indexes, the topic is to big and complex to explain quickly here.