[RESOLVED] most efficient select statement

LordRogaine

I'm using two very large tables, one of which is ten times the size of the other. I have fields in both tables, as an example, in this order field_1, fields_2, field_3 etc. Let's name our two table large_TABLE and very_large_TABLE. My question involves the most efficient order in which I arrange my fields and how I join the two tables. The field "username" is the common field that both tables have.

"select* from large_TABLE, very_large_TABLE where very_large_TABLE.field_1='whatever' and very_large_TABLE.field_2='whatever' and very_large_TABLE.field_3 = 'whatever' and large_TABLE.username=very_large_TABLE.username";

My questions about this are-
1- Is it okay to change the order in which I have the fields in the select statement if I feel it is more efficient that way, or is it counter productive to go against the order in which they appear in the database.

2- The common field both tables have in the "join" is "username". I would think that it is more efficient to compare the smaller table first to the larger table to cut down time. (Better to go through 50,000 usernames on a smaller table than start with 500,000 usernames in a larger table) Would I keep the smaller table first in the order of the select statement?

I hope I was able to make this understood clearly. Thanks for any help or advice.

liquorvicar

Firstly, I wouldn't join on a username (i.e. alphanumeric) field. You much better joining on an ID (i.e. integer) field indexed in both tables.

Second, you can use table aliases to make your query clearer:

"select* from large_TABLE lt, very_large_TABLE vlt where vlt.field_1='whatever' and vlt.field_2='whatever' and vlt.field_3 = 'whatever' and lt.username=vlt.username";

I don't know if your suggestions make much difference to the efficiency of the query. One of the best tools available to you is the EXPLAIN syntax

Piranha

To get the speed of a query use [man]microtime[/man]. The easiest way to know what is best in your database is to test and take time to know how long it takes. Just note that you should test a couple of times and compare the times, don't just test one time.

LordRogaine

The username field is indexed on my tables. Is it alright to change the order in which the fields appear in the select statement even if it doesn't go with the ordeer of the database. Also, if I want to run the username field first in the smaller table against the larger table does it make a difference which table is mentioned first in the select statement?

liquorvicar

Timing your scripts is a very hit or miss way of optimising your queries. Much better (in MySQL anyway) to let the database identify potential hotspots for you.

As regards the order of columns, I can't see it would make a huge difference to the performance.
As regards the order of the tables, it would depend on your WHERE clause and your indexes. Generally, the smaller the first set of data the quicker the query will run. So if you can select just a small number of rows from smaller table (just one, for example if your username is UNIQUE,) then your query will perform better if you can force the database to scan that table first. As I said before, if you're using MySQL, use the EXPLAIN command, it's a great tool.

Generally, if you've got the right indexes for searching and joining then you should be OK, any further optimisation is going to be tiny in comparison (e.g. make sure you are always joining indexed columns!).

LordRogaine

Thank you very much