CODEWITHSUNDEEP
mysql
Prashant
Prashant

Reputation: 27

How to make query fast

I'm using below mentioned query with about 400,000 records in each table its taking about 10~12 seconds to give results. Is there any way I can make it faster.

Query:

select url_details.title, url_details.summary, url_details.id from urls,
url_details where urls.status='' AND urls.keyword_id='2791' AND
url_details.url_id=urls.id

Upvotes: 1

Views: 1531

Answers (5)

Mahesh Patil
Mahesh Patil

Reputation: 1551

Not only creating Index will solve all issues. You can optimize your table structure and remove redundant indexes if there are any. I am using SQLyog which has 'Query Profiler' which will let you know how does your query execution happened, step-by-step approach to query execution (typical layout will look like this), also it has 'Calculate Optimal Datatypes' and 'Find redundant index' which can let you optimized your table structure, I hope this tool will help you. enter image description here)

Upvotes: 0

bukzor
bukzor

Reputation: 38462

The answer to almost all mysql performance problems are indexes. If you know that you'll be filtering on these three variables for all of your queries, add a complex index that spans those three fields.

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

If you will have no idea which indexes will be filtered upon, then add a simple index for each field, and rely on mysql's "index merge" optimization.

http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html

Upvotes: 0

haltabush
haltabush

Reputation: 4528

First, you should better use join syntax :

select url_details.title, url_details.summary, url_details.id from urls join urls_details using(url_id)  where urls.status='' AND urls.keyword_id='2791'

You also need to have somes indexes on urls.status, both url_id and keyword_id. If you have 4 000 000 records on each tables, it will still be a bit slow I guess.

Upvotes: 0

silly
silly

Reputation: 7887

First you have to define the keys on your tables

urls (habe to defined a index with status and keyword_id):

ALTER TABLE `urls` ADD INDEX `index_keyword_status`(`keyword_id`, `status`);

and urls_details.url_id have to be a foreign key OR a index

your select:

SELECT
url_details.title,
url_details.summary,
url_details.id
FROM urls
INNER JOIN url_details
    ON url_details.url_id=urls.id
WHERE urls.keyword_id = 279
    AND LENGTH(urls.status) = 0

Upvotes: 0

gmhk
gmhk

Reputation: 15940

Create INDEXES for the key which you normally compare..That will reduce the time drastically

Upvotes: 1

Related Questions