CODEWITHSUNDEEP
mysqlsqlperformance
abu abu
abu abu

Reputation: 7052

Getting Fastest MySql Query

I am developing a CodeIgniter application with Mysql database which will contain lots of data.

My Query is like below

SELECT id, code, name, category, purchase_price, retail_price, expiry_date, color_code, sub_category FROM products ORDER BY id DESC

In this regard How can I make Fastest SELECT Query ?

How can I measure Query execution time ?

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

For this query:

SELECT id, code, name, category, purchase_price, retail_price, expiry_date, color_code, sub_category
FROM products
ORDER BY id DESC;

There is little you can do from an optimization perspective other than optimizing the ORDER BY. The best index would be:

CREATE INDEX idx_products_id_desc ON products(id desc);

Upvotes: 1

Tom Shaw
Tom Shaw

Reputation: 1712

In order to make the query the absolute fastest is to limit the columns your selecting to what's absolutely necessary, id, price, color etc. Large data sets should always be server side paginated. Client side pagination is almost completely useless in the real world. Before you execute your query run:

SET profiling = 1;

After the query is finished.

SHOW PROFILES;

Upvotes: 1

Related Questions