[SOLVED] Optimize Query!

[jaymc]

I have a table which has 5 million rows. I am running this query which is take 20-50 seconds

 

SELECT f.user, f.friend, f.gender, f.x, f.timestamp, c.propic as image

FROM friends f

INNER JOIN cache c ON f.friend = c.username

WHERE f.user = 'deannahill' AND f.gender = 'Female'

ORDER BY f.x DESC, f.timestamp DESC LIMIT 0,10

 

Here is the EXPLAIN

 

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"

"1","SIMPLE","f","ref","user,friend,user_2","user","27","const","950","Using where; Using filesort"

"1","SIMPLE","c","eq_ref","PRIMARY","PRIMARY","27","DB.f.friend","1",""

 

Notice the filesort

 

I have a covering INDEX on that table which covers all the fields used in the friends table

 

1: MYSQL does not use it, uses an index which is just for the USER field

2: When I use FORCE INDEX it takes just as long

 

The reason it takes so long is the ORDER BY part. Without that its instant

 

Any ideas?

[obsidian]

Not sure, but I believe that it is ordering twice: once for the f.x and once for the f.timestamp. Because the combination of them may not be covered specifically in one index, you may be able to index just those two columns, force that new index and remove your filesort. Try indexing just the x and timestamp columns and try the query again.

[jaymc]

If I just ORDER BY f.x it takes just as long

 

They are both in the covering index too

[obsidian]

If I just ORDER BY f.x it takes just as long

 

They are both in the covering index too

 

What I'm saying is that if the covering index covers all fields, as you mentioned in your OP, the order of the index is not what you are requesting in your ORDER BY. If you force an index that only covers the columns you are ordering by, you are almost certainly going to get a significant reduction in execution time since the results are already in the exact order you are requesting them.

[Mchl]

I think that an order of columns within index has an influence too.

[obsidian]

I think that an order of columns within index has an influence too.

 

Yes. Also, if you are ordering them DESC, your index also has to be declared as DESC or you will have to have a filesort, even with forcing the index, too.

[jaymc]

I think that an order of columns within index has an influence too.

 

Yes. Also, if you are ordering them DESC, your index also has to be declared as DESC or you will have to have a filesort, even with forcing the index, too.

 

Huh? you can do that? How..

 

Perhaps that is the problem because I have none of that

[obsidian]

Huh? you can do that? How..

 

MySql CREATE INDEX is your friend. Try this out:

CREATE INDEX custom_index_01 ON friends (x DESC, timestamp DESC);

 

Then, when you force index, be sure to force it on "custom_index_01" or whatever you decide to call it.

 

When you're dealing with this amount of data, it doesn't hurt to have multiple indexes on a single table in order to help your queries run more optimally. Keep in mind, though, that if you index the columns DESC like I have shown, it will help your current query, but if you ever want to order ASC, this index will not help much.

[jaymc]

Thats really cool!

 

Is there a way to view the current INDEX structure, maybe by default it is already ordered DESC, I use phpmyadmin to create them

 

Maybe you can answer this too.. creating that index will take 40 minutes on my 5 million row table. I dont want any down time.. is there a better way to do this? Someone said create a new table, set up your indexes and then dump the records in but that will still take ages and cause downtime, maybe even loss of new data during that time

 

Please advise if you can

[obsidian]

Hmm... I don't think there is any way to get around that time with an existing table. You could either set up scheduled maintenance, or you could make it a little less invasive by creating the backup table, create the index on the empty table and then let a script run that will throttle the insert of all your friends records into the new table (REPLACE INTO works nicely for this type of thing). This will still take some time, but at least you would not have to be as worried about losing the data... You could even write the contents of the table out to disk and then do an import on the new table instead. The key is, when your index is already in place, each new record is indexed when it is inserted. Applying an index to a table of already 5mil records is just going to take a long time.

 

As for viewing the current index structure, just run this query on the tables you want to examine (SHOW INDEX[/ur]):

SHOW INDEX FROM friends;

[jaymc]

Yeh, perhaps creating a tmp table is the best way, thanks

 

I ran show index and it gave this

 

"Table","Non_unique","Key_name","Seq_in_index","Column_name","Collation","Cardinality","Sub_part","Packed","Null","Index_type","Comment"

"friends","0","PRIMARY","1","id","A","4893046","","","","BTREE",""

"friends","1","user","1","user","A","49424","","","","BTREE",""

"friends","1","friend","1","friend","A","65240","","","","BTREE",""

"friends","1","user_2","1","user","A","70913","","","","BTREE",""

"friends","1","user_2","2","friend","A","4893046","","","","BTREE",""

"friends","1","user_2","3","timestamp","A","4893046","","","","BTREE",""

"friends","1","user_2","4","gender","A","4893046","","","","BTREE",""

"friends","1","user_2","5","x","A","4893046","","","","BTREE",""

 

There is no mention of how its ordered?

[obsidian]

Yes, the Collation column is the order (A is for Ascending), so you have none ordered descending.

 

Also, Fenway is sort of our resident MySql guru, so if he happens to read this, I'd love to get his input to be sure I'm not steering you in the wrong direction, too. I am by no means a DBA

[fenway]

The covering index needs to "cover" (user, x, timestamp) -- no need to worry about asc/desc, mysql can read it in either direction as long as all of the order by fields are going the same way.

[jaymc]

So your saying as long as I am ordering by DESC and then DESC again no need to bother with forcing the order of the index?

 

ORDER BY f.x DESC, f.timestamp DESC = no need to worry?

 

By the way, I read this

 

Lets say you have the query "select b from tbl where a=5" Assuming column "a" is indexed and table is not clustered

by this column MySQL will need to perform at least 2 reads, at first to read index page to find row pointer and when

to read the row itself. If you would have "covering index" (a,b) instead of indexing just column "a" you will have only

index read needed, so you could expect 2 times performance improvement, sometimes a lot more.

 

This says to use a covering index I need to cover fields that are part of the SELECT statement.. in your suggestion it is just fields in the WHERE and ORDER by statement?

[fenway]

So your saying as long as I am ordering by DESC and then DESC again no need to bother with forcing the order of the index?

 

Yes.

 

This says to use a covering index I need to cover fields that are part of the SELECT statement.. in your suggestion it is just fields in the WHERE and ORDER by statement?

There's always a tradeoff with indexing... I'd rather it read some of the table than do a filesort.

[StrangeWill]

"I have a covering INDEX on that table which covers all the fields used in the friends table"

Well, how do you expect it to be more efficient when your index is as large as the table itself? Stick to only searchable items?