January 30th, 2013, 08:01 AM
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 8
Time spent in forums: 54 m 9 sec
Reputation Power: 0
|
|
Quote: | Originally Posted by f_razzoli Moving conditions in the ON clause does the same thing, but that's just because the optimizer is smart  If it would say what you say, it would read unneded rows from the left table.
Oh, WTF, I forgot to tell you about the l.*! Did you remove it? Of course he's right, the more fields you read, the more time the query needs.
But your problem was clear from the EXPLAIN output: MySQL reads 40,000+ rows from the right table. Using a good index will address this problem. Everything else should not have the priority for you.
Can you post the current query, the current explain, and the current show index output? |
Sorry that I've not provided any updates on this for a while. i've been trying to work through the issue, but I'm still stuck with a query that takes 10 seconds 
I tried adding the index SR suggested and running two seperate queries, but this resulted in a combined query time of 12 seconds.
I've also removed the l.* and replaced with actual column names that are required. This reduces the time down to 4 seconds, so an improvement - but the root cause of the slowness is still there in that it is moving data to a temp table for the sort.
If I put the l.* back in there, but remove the ORDER BY clause the query executes in 0.007secs!
So basically I'm back at step 1, with this query:
Code:
SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS l . *
FROM fmd_listings l
INNER JOIN fmd_listings_categories lc ON l.id = lc.list_id
WHERE lc.cat_id =60
AND l.status = 'active'
ORDER BY date DESC , priority DESC
LIMIT 0 , 10
* I run without caching as it was making me thing I fixed it every so often!
My Explain:
Code:
+----+-------------+-------+--------+---------------------------------------------------------+----------+---------+----------------------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows |
+----+-------------+-------+--------+---------------------------------------------------------+----------+---------+----------------------+-------+
| 1 | SIMPLE | lc | ref | PRIMARY,cat_id_2 | cat_id_2 | 4 | const | 46417 |
| 1 | SIMPLE | l | eq_ref | PRIMARY,featured,impressions,fl_ix_status_date_priority | PRIMARY | 4 | flogr_pma.lc.list_id | 1 |
+----+-------------+-------+--------+---------------------------------------------------------+----------+---------+----------------------+-------+
+----------------------------------------------+
| Extra |
+----------------------------------------------+
| Using index; Using temporary; Using filesort |
| Using where |
+----------------------------------------------+
and my indexes...
Code:
mysql> show indexes in fmd_listings;
+--------------+------------+----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+--------------+------------+----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+
| fmd_listings | 0 | PRIMARY | 1 | id | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 0 | gumimports | 1 | gumimport | A | NULL | NULL | NULL | YES | BTREE |
| fmd_listings | 1 | user_id | 1 | user_id | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | title | 1 | title | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | location_id | 1 | location_id | A | 868 | NULL | NULL | | BTREE |
| fmd_listings | 1 | friendly_url | 1 | friendly_url | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | featured | 1 | status | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | featured | 2 | featured | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | featured | 3 | featured_date | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | impressions | 1 | status | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | impressions | 2 | impressions | A | 4 | NULL | NULL | | BTREE |
| fmd_listings | 1 | date | 1 | date | A | 111817 | NULL | NULL | | BTREE |
| fmd_listings | 1 | www | 1 | www | A | 1 | NULL | NULL | | BTREE |
| fmd_listings | 1 | phone | 1 | phone | A | 111817 | NULL | NULL | | BTREE |
| fmd_listings | 1 | coordinates | 1 | latitude | A | 46590 | NULL | NULL | | BTREE |
| fmd_listings | 1 | coordinates | 2 | longitude | A | 186363 | NULL | NULL | | BTREE |
| fmd_listings | 1 | fl_ix_status_date_priority | 1 | id | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | fl_ix_status_date_priority | 2 | status | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | fl_ix_status_date_priority | 3 | date | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | fl_ix_status_date_priority | 4 | priority | A | 559089 | NULL | NULL | | BTREE |
| fmd_listings | 1 | search_txt | 1 | title | NULL | 1 | NULL | NULL | | FULLTEXT |
| fmd_listings | 1 | search_txt | 2 | description_short | NULL | 1 | NULL | NULL | | FULLTEXT |
| fmd_listings | 1 | search_txt | 3 | keywords | NULL | 1 | NULL | NULL | | FULLTEXT |
| fmd_listings | 1 | search_txt | 4 | location_search_text | NULL | 1 | NULL | NULL | | FULLTEXT |
| fmd_listings | 1 | location_search_txt | 1 | location_search_text | NULL | 1 | NULL | NULL | | FULLTEXT |
+--------------+------------+----------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
