MySQL indexing

“Can i just use phpMyAdmin to create indexes and WordPress will automatically use them?”

In theory yes but before you do that why not post the query you are trying to use for the custom fields and mayabe someone can suggest a faster method.

If you are showing us just a few lines of code remember to put the code between backticks but if it is a lot of code then put it into a pastebin and report that link here.

MichaelH, thanks for the reply. I actually did post my query last month but never got a reply – here’s it again:

I wrote a plugin to sort by up to 4 Custom Fields. I use the posts_orderby and posts_join filters. The posts_orderby part of the plugin is straightforward:

$orderby = $sortfield1.’,’.$sortfield2…etc Also has an option for ASC and DESC

The JOIN part goes like this:

LEFT JOIN wp_postsmeta AS $sortfield1 ON ID = post_id AND $sortfield1.meta_key = $sortfield1

This is actually in a loop to create up to 4 JOIN clauses depending on how many custom fields you want to sort by.

The problem is that 1 JOIN is okay, but upon adding the second JOIN my database query goes from < 1 second to 30 seconds or more. Additional JOIN clauses beyond two add a few more seconds.

I’m running MySQL 3.23.

I posted a question on the forums at MySQL and they immediately suggested using an index for the on the meta_key columns.

-matt

So if you add that index, make sure you backup your database before changing anything.

Let us know if it works. Sorry to ask you to repost ??