Could you suggest improvement that I can do in following query. It’s taking 8 seconds to execute and I am afraid it will take more as table fill up with more data.

EXPLAIN SELECT w.Id, w.PartNumber, w.PartDescription, w.CreatedDate, w.ErpKey, w.IsActive, COALESCE(w.LookupManufacturer.Value, ”) AS
Manufacturer, COALESCE(w.LookupUom.Value, ”) AS Uom, w.IsSerialized AS Serialized, w.IsIMU AS Imu, COALESCE(w.LookupAtaChapter.Value,
”) AS AtaChapter, w.LookupGroup.Value AS Group, w.IsCharge
FROM tab.rfqpart AS w
LEFT JOIN tab.lookupgroup AS w.LookupGroup ON w.RefLookupGroupId = w.LookupGroup.Id
LEFT JOIN tab.lookupatachapter AS w.LookupAtaChapter ON w.RefLookupAtaChapterId = w.LookupAtaChapter.Id
LEFT JOIN tab.lookupuom AS w.LookupUom ON w.RefLookupUomId = w.LookupUom.Id
LEFT JOIN tab.lookupmanufacturer AS w.LookupManufacturer ON w.RefLookupManufacturerId = w.LookupManufacturer.Id
WHERE (w.RfqAccountId = ‘eccd587f-9684-42a4-bae3-5b043fbea1f5’) AND ((w.IsCharge = FALSE) AND (w.IsActive = TRUE))
ORDER BY w.PartNumber
LIMIT 20 OFFSET 10;

Possible_Keys: IX_AccountId_IsCharge_IsActive
Key: IX_AccountId_IsCharge_IsActive
Key_Length: 124
ref: cost,const,const
rows: 570677
filtered: 100
Extra: Using temporary; Using filesort

I would appreciate any help you may render.

In the following query, col1: String (6 characters length), col2: Date, col3: Time

Indexes: idx_col1col2 on (col1, col2) and idx_col2col3 on (col2, col3)

select * from force index (idx_col1col2) force index for order by (idx_col2col3) where col1= and col2 >= order by col2 desc, col3 desc limit 1000;

The above query was taking about 30 seconds to a minute to return results. As you can see we used force index hints for where as well as order by.

Could you please advise.

[09-07-2014 07:25:38pm] Fatal Error: MySQL error.Query:SELECT id, friendly_url, IF(date_update!='0000-00-00 00:00:00',date_update,date) AS date, priority FROM pmd_listings WHERE status='active' ORDER BY id ASC LIMIT 180000, 10000Error: (3) Error writing file ‘/var/db/mysql-tmp/MYnTfXMV’ (Errcode: 28) in /home/citydir/public_html/prevpmd/includes/class_database.php on line 132

[09-07-2014 07:27:38pm] Fatal Error: MySQL error.Query:SELECT SQL_CALC_FOUND_ROWS l.* FROM pmd_listings l INNER JOIN pmd_listings_categories lc ON l.id=lc.list_id WHERE lc.cat_id=192 AND l.status='active' ORDER BY priority DESC LIMIT 0,10Error: (126) Incorrect key file for table ‘/var/db/mysql-tmp/#sql_e66b1_0.MYI’; try to repair it in /home/citydir/public_html/prevpmd/includes/class_database.php on line 132

[09-07-2014 07:28:55pm] Fatal Error: MySQL error.Query:SELECT id, friendly_url, IF(date_update!='0000-00-00 00:00:00',date_update,date) AS date, priority FROM pmd_listings WHERE status='active' ORDER BY id ASC LIMIT 1710000, 10000Error: (3) Error writing file ‘/var/db/mysql-tmp/MYeKsGGZ’ (Errcode: 28) in /home/citydir/public_html/prevpmd/includes/class_database.php on line 132

I can’t get a response from the developer who wrote the software, so I’m left to my own devices in finding a resolve.

I would appreciate any help you may render.

=> Allocate 16GB to innodb_buffer_pool_size.
=> Index is there on START_TIME

Query-1
SELECT * FROM ( SELECT a,b,c,d,e,f,g,h,i,j,k,l FROM TEST WHERE START_TIME between ‘2013-11-14 00:00:01’ and ‘2013-12-14 23:59:59’ ORDER BY START_TIME DESC) as TEST_DATA LIMIT 10000;

Above Query => Means selecting all the columns for all the data between 1 month and performing sorting and at last show 10000 records to end user.

Now, my doubt goes: Query-2
SELECT a,b,c,d,e,f,g,h,i,j,k,l FROM TEST WHERE START_TIME between ‘2013-11-14 00:00:01’ and ‘2013-12-14 23:59:59’ ORDER BY START_TIME DESC limit 10000;

Above Query => selecting all the columns from 1 month of data and perform sorting and display the result as soon as 10000 records sorted. (No sorting and buffering of all records).

With Query-1 and Query-2 ->
1> Does these 2 queries will display different result set? Or same?
2> Any other impact on performance?

Because in Query-1, we doing sorting on all records and then display 10k
whereas in Query-2, we doing display 10k sorted records.

Thanks a lot for your help.

We tried the solution base on query see in older post :
SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10

changed to

select * FROM (SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC) as my_table LIMIT 10

It works for us !

You should create another index in this case for category_id. There is always tradeoff between performance and space. Space will always remain cheap compared to the performance gain.

I have one question regarding your comment

“Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try hard to have sorting column(s) to be in the leading table. If ORDER BY is going by field from the table which is not first in the join order index can’t be used.”

I have a similar situation where I can not have sorting columns in leading table; in such case please suggest any work around.

Thanks in anticipation.
Rbk

I am going through your article its found pretty much inserting. I need one help as i am displaying my property query which is displaying properly and ordering the result by following 4 column

p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,””),”2015-12-31″,””) DESC,
p.listing_order DESC,p.propid DESC

In order site, properties are been displayed in this order: Moveup paid, Paid featured, paid, trial then expired. However, we want to show within each group the properties random and make them rotate. Now,
the properties do not rotate so it is not fair for users to see always the same properties in same order. So, we need the rotation to be within same group not mixing all.

Query :-

SELECT DISTINCT(p.propid),p.category_id,p.people_sleep,p.bedrooms,p.bathrooms,p.children,
p.airport,p.features,p.pets,p.smoke, p.wheelchair,p.prop_title,p.property_type,prop_date,
display_calender,other_url_calender,is_feature
FROM property p
LEFT JOIN category ON (p.category_id=category.id)
WHERE p.active IN(“yes”,”d”) AND p.is_feature >= SYSDATE()
ORDER BY p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,””),”2015-12-31″,””) DESC,
p.listing_order DESC,p.propid DESC

Can you please help me into this.