CODEWITHSUNDEEP
mysqlsqlperformancemysql-workbenchquery-performance
IndraJeyan
IndraJeyan

Reputation: 119

MySQL query takes more time to fetch data [MySQL]

I have 500000 records table in my MySQL server. When running a query it takes more time for query execution. sometimes it goes beyond a minute.

Below I have added my MySQL machine detail.

RAM-16GB

Processor : Intel(R) -Core™ i5-4460M CPU @3.20GHz

OS: Windows server 64 bit

I know there is no problem with my machine since it is a standalone machine and no other applications there.

Maybe the problem with my query. I have gone through the MySql site and found that I have used proper syntax. But I don't know exactly the reason for the delay in the result.

SELECT SUM(`samplesalesdata50000`.`UnitPrice`) AS `UnitPrice`, `samplesalesdata50000`.`SalesChannel` AS `Channel`
FROM `samplesalesdata50000` AS `samplesalesdata50000`
GROUP BY `samplesalesdata50000`.`SalesChannel`
ORDER BY 2 ASC
LIMIT 200  OFFSET 0

Can anyone please let me know whether the duration, depends on the table or the query that I have used?

Note: Even if try with indexing, there is no much difference in result time.

Thanks

Upvotes: 2

Views: 1995

Answers (4)

Rick James
Rick James

Reputation: 142433

To improve on OJones's answer, note that

SELECT SalesChannel FROM samplesalesdata50000
    ORDER BY SalesChannel LIMIT 200, 1

will quickly (assuming the index given) find the end of the desired list. Then adding this limits the main query to only the rows needed:

WHERE SalesChannel < (that-select)

There is, however, a problem. If there are fewer than 200 rows in the table, the subquery will return nothing.

You seem to be setting up for "paginating"? In that case, a similar technique can be used to find the starting value:

WHERE SalesChannel >= ...
  AND SalesChannel  < ...

This also avoids using the inefficient OFFSET, which has to read, then toss, all the rows being skipped over. More

But the real solution may be to build and maintain a Summary Table of the data. It would contain subtotals for each, say, month. Then run the query against the Summary table -- it might be 10x faster. More

Upvotes: 0

O. Jones
O. Jones

Reputation: 108796

Two approaches to this:

One approach is to create a covering index on the columns needed to satisfy your query. The correct index for your query contains these columns in this order: (SalesChannel, UnitPrice).

Why does this help? For one thing, the index itself contains all data needed to satisfy your query, and nothing else. This means your server does less work.

For another thing, MySQL's indexes are BTREE-organized. That means they're accessible in order. So your query can be satisfied one SalesChannel at a time, and MySQL doesn't need an internal temporary table. That's faster.

A second approach involves recognizing that ORDER BY ... LIMIT is a notorious performance antipattern. You require MySQL to sort a big mess of data, and then discard most of it.

You could try this:

SELECT SUM(UnitPrice) UnitPrice,
       SalesChannel Channel
  FROM samplesalesdata50000
 WHERE SalesChannel IN (
         SELECT SalesChannel 
           FROM samplesalesdata50000
          ORDER BY Channel LIMIT 200 OFFSET 0
       )
 GROUP BY SalesChannel 
 ORDER BY SalesChannel 
 LIMIT 200 OFFSET 0

If you have an index on SalesChannel (the covering index mentioned above works) this should speed you up a lot, because your aggregate (GROUP BY) query need only consider a subset of your table.

Upvotes: 2

kgzdev
kgzdev

Reputation: 2885

If it was MS SQL Server you would use the WITH (NOLOCK)

and the MYSQL equivalent is

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT SUM(`samplesalesdata50000`.`UnitPrice`) AS `UnitPrice`, `samplesalesdata50000`.`SalesChannel` AS `Channel`
FROM `samplesalesdata50000` AS `samplesalesdata50000`
GROUP BY `samplesalesdata50000`.`SalesChannel`
ORDER BY SalesChannel  ASC
LIMIT 200  OFFSET 0
COMMIT ;

Upvotes: 0

Ермек Нусипов
Ермек Нусипов

Reputation: 1

Your problem with "ORDER BY 2 ASC". Try this "ORDER BY Channel".

Upvotes: -1

Related Questions