Mysql – Query result set

MySQLmysql-5.5

I've 1 query written in our legacy system. (using MySQL 5.5)
Now, with growing data – the below mentioned query taking huge time.
In our system, we've somewhat 2,000,000,000 (2 billion rows) approx 650 GB of data.
Table is partitioned with respect to every day. (which means above query is fetching data from 30 partitions).

Allocate 16GB to innodb_buffer_pool_size.

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).

We have an index on START_TIME.

With Query-1 and Query-2 -> Does these 2 queries will display different result set? Or same?

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.

Best Answer

Look at the Difference

The first query has a subquery that will do an index scan on the START_TIME index that can potentially return more than 10,000 rows.Then, 10,000 rows must be culled from it to produce the result.
The second query would do an index scan and gather no more than 10,000 rows.

Both queries will display the same result, but the second query will do less work and take less processing to achieve.

OBSERVATION #1

If tablename had no indexes, a full table scan would be required.

OBSERVATION #2

If tablename had an index on rank, you still get a full table scan because of the MySQL Query Optimizer ruling out the use of the index because of factors such as key distribution and the possibility of having to lookup each id for every rank during a full index scan.

OBSERVATION #3

If the table had a compound index of (rank,id), then you can a full index scan. In most cases, a full index scan that never references the table for non-indexed columns would be faster than a full index scan that does (See OBSERVATION #2)

OBSERVATION #4

If the query was written slightly different

SELECT rank, COUNT(1) FROM tablename GROUP BY rank

then an index on just the rank column would suffice and produce a full index scan.

CONCLUSION

In light of these observtions, it is definitely a thing of beauty to present to the MySQL Query Optimizer two things:

a good query
proper indexes for all tables in the query

In retrospect, it is also good to give the MySQL Query Optimizer as much of an advantage upfront as possible.

Mysql – Delete data from huge InnoDB table from MySQL Master and Slaves

Here are some things you should consider:

CONSIDERATION #1

Using DELETE FROM tbl ... LIMIT 5000 is not replication-safe because the 5000 rows being deleted may not be the same order or the same set of 5000 rows on a slave. See MySQL Documentation (Replication and LIMIT). Should a DELETE on a Master get interrupted, it has to rollback. In that event, it could be remotely possible that the number of times the DELETE FROM tbl ... LIMIT 5000 is called may not be the same. Extra calls of the DELETE on a Slave is not critical. Too few calls could be bad. This should not happen.

SUGGESTION : Make sure the row count for RTesAll is identical on the Master and all Slaves.

CONSIDERATION #2

Since you are running a Stored Procedure on the Master, the DELETE command will not begin its execution on a Slave until the completion of the DELETE on the Master and the DELETE command is posted in the Master's Binary Logs. With one Master and 4 Slaves (5 DB Servers), there are a certain of calls to DELETE multiplied by 5.

SUGGESTION #1 : You should add set sql_log_bin = 0; to the Stored Procedure

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;
    
    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;          
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

to prevent all DELETEs from replicating. If you are deleting 611992998 rows, deleting every 5000 rows requires executing DELETE ... LIMIT 5000 122399 times (That's 611992998 / 5000 rounded up to next integer).

This will prevent having to write to the binlogs 122399 times.

SUGGESTION #2 : Run the Stored Procedure on the Master and Slaves at the same time.

This prevents replication lag
This gets rid of all the rows at the same time
This is best done during maintenance cycles or slow read times

CONSIDERATION #3

Make sure autocommit is enabled on Master and Slaves. Otherwise, all the deletes will pile up in the undo logs inside ibdata1 before either doing all the deletes at once as a single transaction or a huge rollback.