Urgent query optimization

help in select query

[tabish]

Hi all,

I have been facing a real bad issue for past few days. Because of this my server is under so much load. There are two queries written in a page:

First query runs:

$firstquery=mysql_query("select username,photo,picture_type,i_am,seeking_a,zodiac,city from table1 where country='United States' and block_by_webmaster='no' and block_profile='no' and approved='yes' and step1='yes' ORDER BY photo desc,user_index DESC");

when this query runs: under the while loop of above query we have this:

while ($row=mysql_fetch_object($firstquery) ){
$second_query=mysql_query("select title,about_myself from $table2 where username='$row[username]'");
//Some display here

}

Now.. I have about 200000 data in both tables and this query is taking more than 35 seconds to run.

Can anyone simplify this query and just combine it in one query with optimization?

I am real worried.. please help me guys.

Regards

[phranque]

it would be faster if you also had a "user_index" column in table2 and that column (or at worst the username column) had an index on it in both tables.

it would also probably be faster to to skip the loop and do a join.
something like this would probably work:

select table1.username,photo,picture_type,i_am,seeking_a,zodiac,city,title,about_myself from table1,table2 where table1.username=table2.username and country='United States' and block_by_webmaster='no' and block_profile='no' and approved='yes' and step1='yes' ORDER BY photo desc,table1.user_index DESC

or ... where table1.user_index=table2.user_index and ...

in general it's a good idea to use EXPLAIN when you have a mysql performance issue;
Optimizing Queries with EXPLAIN [dev.mysql.com]

[tabish]

The query you explained doesnt work.. says
Column 'photo' in field list is ambiguous

:(

[phranque]

try table1.photo - you will need to disambguate all columns that are common to both tables.

[tabish]

When I use Explain it says:

id: 1
select_type: SIMPLE
table: table1
type: ref
possible_keys: country,username
key: country
key_len: 26
ref: const
rows: 20880
Extra: Using where; Using filesort

can you see buddy.. if it looks ok?

Regards

[rocknbil]

Column 'photo' in field list is ambiguous

As phranque said, this is because you need to add "tablename." before photo and any other fields in a multiple table query. You are selecting on two tables and it's telling you it can't distinguish between "photo" in table 1 and "photo" in table 2. This follows through **all** field references.

select table1.username,table1.photo,table1.picture_type,table1.i_am, table1.seeking_a,table1.zodiac,table1.city,table1.title,table1.about_myself from table1,table2 where table1.username=table2.username and table1.country='United States' and table1.block_by_webmaster='no' and table1.block_profile='no' and table1.approved='yes' and table1.step1='yes' ORDER BY table1.photo desc,table1.user_index DESC

But you have bigger issues. These are probably more important.

Point 1: Use numeric types wherever possible.

Integer fields will **always** be faster than textual fields. For small databases this won't show it's improvement, but as it gets large it makes a **big** difference. So,

country='United States'

You should have a relational table, "countries" (don't use such an obvious name) that has a unique id assigned to "United States"

rec_id¦co_id¦code¦fullname
..........
25¦234¦US¦United States

Then extract code, value, whatever as you need them. In table1, you can now store an integer value for country.

The same is true of

block_by_webmaster='no' and block_profile='no' and approved='yes' and step1='yes'

These should be boolean "true/false", enum, or, at the very least, tinyint(1). I like to use tinyint because I never assume my implementation is the "final word"; in the future maybe I want to add "maybe" or "banned" or "invalid" as a value and don't have to alter the database to do so (see example below).

Somewhere in your code, you can easily convert these to textual values,

PHP
$YesNos=Array('no', 'yes', 'maybe', 'banned', 'invalid'); // 0 = no, 1 = yes, 2 = maybe, 3 = banned, 4 = invalid

So when you print results, you just do

echo "$YesNos[$row['block_profile']]";

OOPS! Customer wants these capitalized. Again, no changes to DB:

$YesNos=Array('No', 'Yes', 'Maybe', 'Banned', 'Invalid');

Note that the "$YesNos" can apply to any numeric value (in my example, 0-4,) from the database or from your programming, so if it's restricted to yes and no, you can use this array for block_by_webmaster, block_profile, approved, and step1 - unless you extend it like I've shown. Even so, some may never get to indexes 2+ of the array and may still apply.

So your query can now be

country=234 and block_by_webmaster=0 and block_profile=0 and approved=1 and step1=1

Noting that quotes are not required on numeric field types (but they shouldn't hurt anything if left in.)

If you do this you will be amazed at the difference in speed. You're probably recoiling in horror, OMG, I can't re-enter 200K records - it's easy. Create a new table, then create the country table, write a small script to move all the data over to the new table. As you insert the country, get the last ID, update the record in the new table with that ID. After all is said and done, rename the new table, drop the old.

Point 2: column indexes

Indexing creates faster lookups on **any** field. If you haven't done column indexing, execute these queries, see what it does for performance. Note that these are without making the changes mentioned above, indexing your table1 "as is:"

alter table table1 add index country (country(6));
alter table table1 add index block_by_webmaster (block_by_webmaster(1));
alter table table1 add index block_profile (block_profile(1));
alter table table1 add index block_profile (approved(1));
alter table table1 add index step1 (step1(1));

Then see if your query speeds up. Some notes on indexing:

- In the above examples, you don't need an index width on integer or date/datetime fields (date fields as of 5.0, I think, or possibly one of the 4.+ versions, can't remember the exact break. Previous versions read date fields as text..) So if you make the suggested changes, it would be
alter table table1 add index country;
(etc.)

- Indexing makes selecting faster, but insert/update a little slower. So for this reason, Only index columns on which you expect searches. Unless your inserts/updates go through an entire table, the sacrifice is usually trivial.

- I've never found a **good** answer to "How wide should my index be on text fields?" It's always "whatever works best." If you have a 2 character text field, like country code, an index is a waste. But in your example, a full country name, it will likely increase performance. I've used "6" in my example, and use that for indexes on email addresses, etc., but on narrower fields, say varchar(6), I'd use (3) or something. Find the optimum width and keep it as small as possible.

My index examples with (1),

alter table table1 add index block_profile (approved(1));

are expecting "no" or "yes" and it's entirely possible these are so small indexing may not improve performance.

[rocknbil]

Error:

alter table table1 add index block_profile (approved(1));

should be

alter table table1 add index approved (approved(1));

[tabish]

You ROCK rocknbil.. thank you.

I will try to implement these things.

Thank you so much dude..