Optimize Select Query from a table with Millions Rows

Question

I am developing a website with Wordpress self-hosted CMS.

In one of the page, i ran a function that do a query into wordpress database, to check wether a post is already posted or not, i am comparing the title to check it.

Here is my query:

$wpdb->get_row("SELECT id FROM wp_posts WHERE post_title = '" . $title . "'", 'ARRAY_A');

So i am checking whether $title is posted or not, but i am afraid if the number of post grows, let says 1 Million Posts, i am afraid that it will be very slow..

Any suggestion on how to make this query faster? i heard about CREATE INDEX and mysql caching but i don't understand how to implement it.. any explanations and references suggestion will be highly appreciated.

Answer 1

Try this:

CREATE INDEX IX_wp_posts_post_title ON wp_posts (post_title)

The creation of the index will take a long time but afterward your queries should be close to instant.