CODEWITHSUNDEEP
mysqloptimizationindexingquery-optimization
hackartist
hackartist

Reputation: 5264

MYSQL need a faster query

I have two tables, one of which matches ids to ratings and one of which has the ids with the actual data in about 15 columns. In the data table the id column has an index and in the ratings table the id is the primary key and the rating is indexed. I want to select data but only from the rows which have a positive rating. to do so I am using the query

SELECT * FROM data_table 
  INNER JOIN rating_table ON data_table.id = rating_table.id
  WHERE rating > 0

but for some reason this is taking about 0.35 seconds which seems very long to me. There are about 90,000 rows in the data table and about 5,000 rows in the rating table and this needs to be taking much less than even a tenth of a second... How can I either index differently or query differently to speed this task up?

edit: After profiling it gave me the following. Note that I think it was cached so the query came back much faster than before but even so this might be useful to someone

0.000012 starting   
0.000053 checking query cache for query     
0.000014 Opening tables     
0.000006 System lock    
0.000027 Table lock     
0.000044 init   
0.000018 optimizing     
0.000060 statistics     
0.000016 preparing  
0.000004 executing  
0.004916 Sending data   
0.000007 end    
0.000003 query end  
0.002271 freeing items  
0.000009 storing result in query cache  
0.000002 logging slow query     
0.000004 cleaning up

so I see a lot of the time was spent in sending data... how can I speed this part up?

Upvotes: 1

Views: 141

Answers (1)

Bjoern
Bjoern

Reputation: 16304

No exact solution to your problem, but some advice without seeing the actual table structure: Profile your Query properly.

MySQL has an inbuild profiler, which allows you to see very detailed for what part of the query how much time has been spend.

In your case, follow these steps:

(1) Execute your query.

(2) Find out the query id for profiling:

SHOW PROFILES;

It will return you something like this:

Query_ID |  Duration | Query
---------+-----------+-----------------------
   2     | 0.0006200 | SHOW STATUS
   3     | 0.3600000 | (your query here)
  ...    | ...       | ...

Now you know the query id is (3).

(3) Profile the query.

SHOW PROFILE FOR QUERY 3;   // example

This will return you the details, which might look like this:

Status                          | Duration
--------------------------------+-------------------
starting                        | 0.000010
checking query cache for query  | 0.000078
Opening tables                  | 0.000051
System lock                     | 0.000003
Table lock                      | 0.000008
init                            | 0.000036
optimizing                      | 0.000020
statistics                      | 0.000013
preparing                       | 0.000015
Creating tmp table              | 0.000028
executing                       | 0.000602
Copying to tmp table            | 0.000176
Sorting result                  | 0.000043
Sending data                    | 0.080032
end                             | 0.000004
removing tmp table              | 0.000024
end                             | 0.000006
query end                       | 0.000003
freeing items                   | 0.000148
removing tmp table              | 0.000019
closing tables                  | 0.000005
logging slow query              | 0.000003
cleaning up                     | 0.000004

In this example, most of the time was actually spend sending the data from the server back to the client.

If you've done this, maybe update your question to display the results. And maybe add the data structure, too.

Upvotes: 3

Related Questions