MySQL – Optimizing Query for Large Number of Rows

indexMySQLperformanceperformance-tuning

I have the following database table in MySQL :

enter image description here

and is running the following query

SELECT `scratch_tickets`.`id` AS ticket_id, `scratch_tickets`.`created_at`, `scratch_tickets`.`prize_type`, `scratch_tickets`.`prize`, `scratch_tickets`.`is_win`, `scratch_tickets`.`game_id` FROM (`scratch_tickets`)  WHERE `scratch_tickets`.`created_at` >='2015-02-12 00:00:00' and created_at<='2015-02-18 23:59:59' AND `scratch_tickets`.`status` = 4 LIMIT 50;

Now the EXPLAIN is giving following results

enter image description here

The query is taking almost 35 seconds to execute and when I use this with PHP, it is getting timed out. Here I think the indexes being utilized. How can I optimize this for a better performance ?

The table already have 25708614 records.

Best Answer

Your query:

SELECT scratch_tickets.id AS ticket_id, 
       -- more columns
FROM (scratch_tickets)  
WHERE scratch_tickets.created_at >='2015-02-12 00:00:00' 
  AND created_at <= '2015-02-18 23:59:59' 
  AND scratch_tickets.status = 4 
LIMIT 50 ;

needs a simple index on (status, created_at):

ALTER TABLE scratch_tickets
  ADD INDEX status_created_at_idx         -- pick a name for the index
  (status, created_at) ;

After testing that the query is using the new index and is more efficient, you can probably drop the old status index. ("probably" because you may have some rare query that profits a lot from a (status) index but is slow when using the (status, created_at) one.)

Some more notes:

Having LIMIT without ORDER BY is a recipe for indeterminate results. Always use ORDER BY so you are in control of which (50) rows will be chosen for return (unless you really don't mind semi-random results or there are serious performance issues).
Also note that adding or changing the ORDER BY expression may affect performance. For the specific query, the index will be used effectively if you have ORDER BY created_at or ORDER BY created_at DESC or ORDER BY created_at, ticket_id. If a different column or expression is used, the efficiency may not be as good.
Instead of inclusive (>= and <= - or BETWEEN) it's easier and less error-prone to use inclusive-exclusive ranges for datetime types. This way, you don't care much if the type is DATE, DATETIME or TIMESTAMP or if it has second, millisecond or microsecond accuracy.

Queries are more clear with aliases. See a rewrite:

SELECT st.id AS ticket_id, 
       -- more columns
FROM scratch_tickets AS st
WHERE at.created_at >= '2015-02-12 00:00:00'       -- you could even use '2015-02-12'
  AND st.created_at <  '2015-02-19 00:00:00'       -- notice the   < 
  AND st.status = 4 
ORDER BY <some_expression>
LIMIT 50 ;

Related Solutions

Mysql – Optimizing ORDER BY for simple MySQL query

The EXPLAIN SELECT you posted definitely seems counter-intuitive.

If your query included WHERE s.id = ... then the query plan you're seeing might make a little bit more sense, but I'm assuming you're not.

It looks like the optimizer is getting distracted by the facts that supplier is a smaller table and that the supplier_id index in the po table can be used as a covering index... and with those facts in hand, it's overlooking the seemingly-obvious fact that the tables should be read in the opposite order than the one it chooses.

Here are two alternatives.

-- use the STRAIGHT_JOIN directive to insist that the optimizer process the tables in only the listed order:

SELECT STRAIGHT_JOIN * FROM `po` 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

-- use the FORCE KEY index hint to direct the optimizer to prefer the primary key of the po table:

SELECT * FROM `po` FORCE KEY (PRIMARY) 
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

The first option is probably the better option, since FORCE KEY, in spite of the name, is still only a "hint" that the optimizer can choose to ignore, while STRAIGHT_JOIN genuinely does force the hand of the optimizer to join the tables in the order they're listed.

Mysql – Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

If you ONLY want to optimise for this query. This is the best index:

ALTER TABLE items ADD INDEX (category, created_at, user_id)

This optimises the value of the filters, which reduces the total amount of data you touch. By adding user_id, item_id at the end of the query, you make the index covering and it saves you a lookup into the primary index.

We can assume that item_id is NOT NULL (as it is the PRIMARY index).

However, because the MySQL optimiser is pretty stupid, you may need to rewrite like this:

SELECT  user_id, SUM(count) AS count
FROM
(
  SELECT category, created_at, user_id, COUNT(*) as count
  FROM items
  WHERE category = 'magazine'
  AND created_at > 1384754400
  GROUP BY category, created_at, user_id
) AS d
GROUP BY user_id
ORDER BY count DESC
LIMIT 100

Best Answer

Related Solutions

Mysql – Optimizing ORDER BY for simple MySQL query

Mysql – Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

Related Question