mysql join query problem

Question

I have 2 tables
1) "products" with fields (productid PK, name, description, price)
2) "sales" with fields (salesid PK, salestime, productid, customername, customeremail, status)

I need to display data in table format as

SalesID Product Name Amount Customer Name Customer Address Payment Status

For this, I am using following query

SELECT s.salesid, p.name, p.price, s.customername, s.customeremail, s.status 
FROM sales s 
LEFT JOIN products p ON s.productid = p.productid 
ORDER BY salestime DESC 
LIMIT 0, 15

Is there any way I can still optimize this query to run faster?

JOIN s and ORDER BY s are where you will see a slow down, but if you changed those, you wouldn't get back the data you want, in the order you want it. The query looks good to me, but an index on the salestime and product_id columns would probably help speed things up a bit. Also, can you store this query as a view or stored procedure? When a SQL is called, the server needs to compile the SQL code, then execute it. When it's called from a view or stored proc, it's precompiled by the server, which could save a few more milliseconds.. — bakoyaro
– bakoyaro, Commented Nov 24, 2010 at 5:00

Adriaan Stander · Accepted Answer · 2010-11-24 04:38:01Z

2

Do you have the appropriate indexes on the tables?

Have a look at CREATE INDEX Syntax and How MySQL Uses Indexes

answered Nov 24, 2010 at 4:38

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I-M-JM Over a year ago

if tables are already filled with data, and I index them they won't make any difference? should I remove all data, then create index or is there any other way around?

Adriaan Stander Over a year ago

NO no no no. Creating the index with data in the tables should be fine.

jon_darkstar · Accepted Answer · 2010-11-24 04:42:36Z

0

The query is as good as it can be. By their nature queries specify WHAT to do, not HOW to do it.

It's the RDMS below it that will effect your performance, and the main way to impact a query like this is to add indexes the columns on which you join (each table's oroductid)

answered Nov 24, 2010 at 4:42

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

1 Comment

jon_darkstar Over a year ago

depends on the result you want. if no match is made between the two tables for a certain row (of your 'left' table) do you want these fields to be displayed along with nulls from the counterpart in the other table (LEFT JOIN)? or do you just want it to not be listed (INNER JOIN, of which EQUI is a type)?

D'Arcy Rittich · Accepted Answer · 2010-11-24 04:52:07Z

0

The query is fine. Try indexing productid in both tables, as well as salestime.

answered Nov 24, 2010 at 4:52

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

2 Comments

I-M-JM Over a year ago

if tables are already filled with data, and I index them they won't make any difference? should I remove all data, then create index or is there any other way around?

D'Arcy Rittich Over a year ago

Just add the indexes and they'll index the existing data.

Collectives™ on Stack Overflow

mysql join query problem

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related