Help with String Expressions

[McGruff]

I\'m having trouble understanding how MySQL optimizes SELECT statements.

 

Say I have 3 tables: orders, customers, and products. The primary key for the orders table is order_number, keep that in mind. I want to select all the information about a certain order number. To make things easier on whoever enters the order number, I want to use string expressions to get as many order #\'s that might enter what the user enters. Here is an exaple sql statement:

 

SELECT o.order_number, c.customer_name, p.product_name

FROM orders AS o, customers AS c, products AS p

WHERE o.order_number LIKE \'%$whatever%\'

AND c.customer_id=o.customer_id

AND p.product_id=o.product_id

 

the statement above takes ages to execute when customers gets large. Why? because MySQL grabs all of the rows from customers, then matches the orders and products to those rows. This behavior does not happen if I change

\"WHERE o.order_number LIKE \'%$whatever%\'\"

to

\"WHERE o.order_number=\'$whatever\'\"

 

if you make that change, MySQL sees o.order_number is primary, so will only return 1 possible row, and then grabs the results fast. So why does it not do the same when using LIKE? I think MySQL is assuming if it grabs the matching order numbers, it will take longer, but unless someone enters a single digit order number, this will not be the case. Any suggestions/opinions?

[effigy]

run your select statement prefixed by EXPLAIN.

 

further info: http://www.mysql.com/doc/en/EXPLAIN.html

 

i think the issue is that WHEREs which use %s cannot be indexed; whereas \'1\' or \'2\' is a lot easier.

 

what if you reorder the WHERE?

 

from:

 

WHERE o.order_number LIKE \'%$whatever%\'

AND c.customer_id=o.customer_id

AND p.product_id=o.product_id

 

to:

 

WHERE c.customer_id=o.customer_id

AND p.product_id=o.product_id

AND o.order_number LIKE \'%$whatever%\'

 

:?: just an idea

 

let me know what you find--i\'d be interested

[McGruff]

I\'ve tried changing around the order in both the FROM list and the WHERE list, but that doesn\'t seem to have any effect.

 

If I do the LIKE version , here are the explained results:

 

table type possible_keys key key_len ref rows Extra

c All Primary NULL NULL NULL 95264 temp, filesort

o ref Primary c_id c_id 4 c.c_id 1 where used

p ref o_id o_id 4 o.oid 1 using index

 

 

If I do the = version:

table type possible_keys key key_len ref rows Extra

o const Primary,c_id Primary 4 const 1

c const Primary Primary 4 const 1

p const o_id o_id 4 const 1

 

 

I hope that is somewhat legible.

 

It is pretty easy to see why the second version is faster. MySQL knows that the second version will return only 1 row from each table, so it grabs the results fast. In the 1st version, MySQL doesn\'t know how many rows it is going to return, so it opts to do a full scan of the table it thinks will be the smallest, which is the customers © table. I believe MySQL is choosing the wrong table. If it does a scan on the orders table, filtering out orders that do not match the LIKE results, there will be (in most cases) a lot less rows to scan through. My problem is I don\'t know how to let MySQL know this.

[effigy]

sorry mcgruff--i\'m not brushed up on this very much. i am in the process of moving and have my books packed up i think :x i\'ll keep looking...

 

try this? :roll:

 

SELECT o.order_number, c.customer_name, p.product_name

FROM orders AS o, customers AS c, products AS p

WHERE (c.customer_id=o.customer_id AND p.product_id=o.product_id) AND o.order_number LIKE \'%$whatever%\'

 

keep digging through that explain link i provided:

 

You can also see if the optimiser joins the tables in an optimal order. To force the optimiser to use a specific join order for a SELECT statement, add a STRAIGHT_JOIN clause.

 

For non-simple joins, EXPLAIN returns a row of information for each table used in the SELECT statement. The tables are listed in the order they would be read. MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table and so on. When all tables are processed, it outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

 

perhaps if you changed the SQL syntax to the LEFT JOIN ON... RIGHT JOIN ON and so on... maybe that would force the order and the search like the above mentions with the STRAIGHT_JOIN.