MySQL doesn't end?

[sonicthehedgehog]

I've just been told that a query is too long and doesn't end but I don't understand what that means or how to rectify it?

This is the query I'm using (which I do admit is a large query but I need to get all of the info in it)

$sql_select = "SELECT * "; $sql_from = " FROM feeds, product_categories_map, type, source, sweeteners, flavour, ingredients, dietary, shipping, merchant WHERE feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and shipping.country_id=".dbstr( $_POST['country'] )." AND ( product_feed_id != 1 ";$sql_group = " GROUP BY feeds.product_name";$order=$_POST['order'];if ($_POST['order']<>""){ $sql_orderby = " ORDER BY feeds.$order ASC"; } else {$sql_orderby = " ORDER BY feeds.product_name DESC";}//$sql_orderby = " ORDER BY feeds.price ASC";if ($_POST['muscle']<>""){ $sql_where .= " OR category_id=1 "; } if ($_POST['performance']<>""){ $sql_where .= " OR category_id=2 "; } if ($_POST['diet']<>""){ $sql_where .= " OR category_id=3 "; } if ($_POST['lean']<>""){ $sql_where .= " OR category_id=4 "; } if ($_POST['energy']<>""){ $sql_where .= " OR category_id=5 "; } if ($_POST['health']<>""){ $sql_where .= " OR category_id=6 "; } if ($_POST['type_1']<>""){ $sql_where .= " OR type_id=1 "; } if ($_POST['type_2']<>""){ $sql_where .= " OR type_id=2 "; } if ($_POST['type_3']<>""){ $sql_where .= " OR type_id=3 "; } if ($_POST['type_4']<>""){ $sql_where .= " OR type_id=4 "; } if ($_POST['type_5']<>""){ $sql_where .= " OR type_id=5 "; } if ($_POST['type_6']<>""){ $sql_where .= " OR type_id=6 "; } if ($_POST['type_7']<>""){ $sql_where .= " OR type_id=7 "; } if ($_POST['type_8']<>""){ $sql_where .= " OR type_id=8 "; } if ($_POST['type_9']<>""){ $sql_where .= " OR type_id=9 "; } if ($_POST['type_10']<>""){ $sql_where .= " OR type_id=10 "; } if ($_POST['type_11']<>""){ $sql_where .= " OR type_id=11 "; } if ($_POST['type_12']<>""){ $sql_where .= " OR type_id=12 "; } if ($_POST['type_13']<>""){ $sql_where .= " OR type_id=13 "; } if ($_POST['type_14']<>""){ $sql_where .= " OR type_id=14 "; } if ($_POST['type_15']<>""){ $sql_where .= " OR type_id=15 "; } if ($_POST['type_16']<>""){ $sql_where .= " OR type_id=16 "; } if ($_POST['type_17']<>""){ $sql_where .= " OR type_id=17 "; } if ($_POST['type_18']<>""){ $sql_where .= " OR type_id=18 "; } if ($_POST['type_19']<>""){ $sql_where .= " OR type_id=19 "; } if ($_POST['type_20']<>""){ $sql_where .= " OR type_id=20 "; } if ($_POST['source_1']<>""){ $sql_where .= " OR source_id=1 "; } if ($_POST['source_2']<>""){ $sql_where .= " OR source_id=2 "; } if ($_POST['source_3']<>""){ $sql_where .= " OR source_id=3 "; } if ($_POST['source_4']<>""){ $sql_where .= " OR source_id=4 "; } if ($_POST['source_5']<>""){ $sql_where .= " OR source_id=5 "; } if ($_POST['source_6']<>""){ $sql_where .= " OR source_id=6 "; } if ($_POST['source_7']<>""){ $sql_where .= " OR source_id=7 "; } if ($_POST['source_8']<>""){ $sql_where .= " OR source_id=8 "; } if ($_POST['source_9']<>""){ $sql_where .= " OR source_id=9 "; } if ($_POST['source_10']<>""){ $sql_where .= " OR source_id=10 "; } if ($_POST['source_11']<>""){ $sql_where .= " OR source_id=11 "; } if ($_POST['source_12']<>""){ $sql_where .= " OR source_id=12 "; } if ($_POST['source_13']<>""){ $sql_where .= " OR source_id=13 "; } if ($_POST['source_14']<>""){ $sql_where .= " OR source_id=14 "; } if ($_POST['source_15']<>""){ $sql_where .= " OR source_id=15 "; } if ($_POST['source_16']<>""){ $sql_where .= " OR source_id=16 "; } if ($_POST['source_17']<>""){ $sql_where .= " OR source_id=17 "; } if ($_POST['source_18']<>""){ $sql_where .= " OR source_id=18 "; } if ($_POST['source_19']<>""){ $sql_where .= " OR source_id=19 "; } if ($_POST['source_20']<>""){ $sql_where .= " OR source_id=20 "; } if ($_POST['sweeteners_1']<>""){ $sql_where .= " OR sweeteners_id=1 "; } if ($_POST['sweeteners_2']<>""){ $sql_where .= " OR sweeteners_id=2 "; } if ($_POST['sweeteners_3']<>""){ $sql_where .= " OR sweeteners_id=3 "; } if ($_POST['sweeteners_4']<>""){ $sql_where .= " OR sweeteners_id=4 "; } if ($_POST['sweeteners_5']<>""){ $sql_where .= " OR sweeteners_id=5 "; } if ($_POST['sweeteners_6']<>""){ $sql_where .= " OR sweeteners_id=6 "; } if ($_POST['sweeteners_7']<>""){ $sql_where .= " OR sweeteners_id=7 "; } if ($_POST['sweeteners_8']<>""){ $sql_where .= " OR sweeteners_id=8 "; } if ($_POST['sweeteners_9']<>""){ $sql_where .= " OR sweeteners_id=9 "; } if ($_POST['sweeteners_10']<>""){ $sql_where .= " OR sweeteners_id=10 "; } if ($_POST['sweeteners_11']<>""){ $sql_where .= " OR sweeteners_id=11 "; } if ($_POST['sweeteners_12']<>""){ $sql_where .= " OR sweeteners_id=12 "; } if ($_POST['sweeteners_13']<>""){ $sql_where .= " OR sweeteners_id=13 "; } if ($_POST['sweeteners_14']<>""){ $sql_where .= " OR sweeteners_id=14 "; } if ($_POST['sweeteners_15']<>""){ $sql_where .= " OR sweeteners_id=15 "; } if ($_POST['sweeteners_16']<>""){ $sql_where .= " OR sweeteners_id=16 "; } if ($_POST['sweeteners_17']<>""){ $sql_where .= " OR sweeteners_id=17 "; } if ($_POST['sweeteners_18']<>""){ $sql_where .= " OR sweeteners_id=18 "; } if ($_POST['sweeteners_19']<>""){ $sql_where .= " OR sweeteners_id=19 "; } if ($_POST['sweeteners_20']<>""){ $sql_where .= " OR sweeteners_id=20 "; } if ($_POST['flavour_1']<>""){ $sql_where .= " OR flavour_id=1 "; } if ($_POST['flavour_2']<>""){ $sql_where .= " OR flavour_id=2 "; } if ($_POST['flavour_3']<>""){ $sql_where .= " OR flavour_id=3 "; } if ($_POST['flavour_4']<>""){ $sql_where .= " OR flavour_id=4 "; } if ($_POST['flavour_5']<>""){ $sql_where .= " OR flavour_id=5 "; } if ($_POST['flavour_6']<>""){ $sql_where .= " OR flavour_id=6 "; } if ($_POST['flavour_7']<>""){ $sql_where .= " OR flavour_id=7 "; } if ($_POST['flavour_8']<>""){ $sql_where .= " OR flavour_id=8 "; } if ($_POST['flavour_9']<>""){ $sql_where .= " OR flavour_id=9 "; } if ($_POST['flavour_10']<>""){ $sql_where .= " OR flavour_id=10 "; } if ($_POST['flavour_11']<>""){ $sql_where .= " OR flavour_id=11 "; } if ($_POST['flavour_12']<>""){ $sql_where .= " OR flavour_id=12 "; } if ($_POST['flavour_13']<>""){ $sql_where .= " OR flavour_id=13 "; } if ($_POST['flavour_14']<>""){ $sql_where .= " OR flavour_id=14 "; } if ($_POST['flavour_15']<>""){ $sql_where .= " OR flavour_id=15 "; } if ($_POST['flavour_16']<>""){ $sql_where .= " OR flavour_id=16 "; } if ($_POST['flavour_17']<>""){ $sql_where .= " OR flavour_id=17 "; } if ($_POST['flavour_18']<>""){ $sql_where .= " OR flavour_id=18 "; } if ($_POST['flavour_19']<>""){ $sql_where .= " OR flavour_id=19 "; } if ($_POST['flavour_20']<>""){ $sql_where .= " OR flavour_id=20 "; } if ($_POST['ingredients_1']<>""){ $sql_where .= " OR ingredients_id=1 "; } if ($_POST['ingredients_2']<>""){ $sql_where .= " OR ingredients_id=2 "; } if ($_POST['ingredients_3']<>""){ $sql_where .= " OR ingredients_id=3 "; } if ($_POST['ingredients_4']<>""){ $sql_where .= " OR ingredients_id=4 "; } if ($_POST['ingredients_5']<>""){ $sql_where .= " OR ingredients_id=5 "; } if ($_POST['ingredients_6']<>""){ $sql_where .= " OR ingredients_id=6 "; } if ($_POST['ingredients_7']<>""){ $sql_where .= " OR ingredients_id=7 "; } if ($_POST['ingredients_8']<>""){ $sql_where .= " OR ingredients_id=8 "; } if ($_POST['ingredients_9']<>""){ $sql_where .= " OR ingredients_id=9 "; } if ($_POST['ingredients_10']<>""){ $sql_where .= " OR ingredients_id=10 "; } if ($_POST['ingredients_11']<>""){ $sql_where .= " OR ingredients_id=11 "; } if ($_POST['ingredients_12']<>""){ $sql_where .= " OR ingredients_id=12 "; } if ($_POST['ingredients_13']<>""){ $sql_where .= " OR ingredients_id=13 "; } if ($_POST['ingredients_14']<>""){ $sql_where .= " OR ingredients_id=14 "; } if ($_POST['ingredients_15']<>""){ $sql_where .= " OR ingredients_id=15 "; } if ($_POST['ingredients_16']<>""){ $sql_where .= " OR ingredients_id=16 "; } if ($_POST['ingredients_17']<>""){ $sql_where .= " OR ingredients_id=17 "; } if ($_POST['ingredients_18']<>""){ $sql_where .= " OR ingredients_id=18 "; } if ($_POST['ingredients_19']<>""){ $sql_where .= " OR ingredients_id=19 "; } if ($_POST['ingredients_20']<>""){ $sql_where .= " OR ingredients_id=20 "; } if ($_POST['dietary_1']<>""){ $sql_where .= " OR dietary_id=1 "; } if ($_POST['dietary_2']<>""){ $sql_where .= " OR dietary_id=2 "; } if ($_POST['dietary_3']<>""){ $sql_where .= " OR dietary_id=3 "; } if ($_POST['dietary_4']<>""){ $sql_where .= " OR dietary_id=4 "; } if ($_POST['dietary_5']<>""){ $sql_where .= " OR dietary_id=5 "; } if ($_POST['dietary_6']<>""){ $sql_where .= " OR dietary_id=6 "; } if ($_POST['dietary_7']<>""){ $sql_where .= " OR dietary_id=7 "; } if ($_POST['dietary_8']<>""){ $sql_where .= " OR dietary_id=8 "; } if ($_POST['dietary_9']<>""){ $sql_where .= " OR dietary_id=9 "; } if ($_POST['dietary_10']<>""){ $sql_where .= " OR dietary_id=10 "; } if ($_POST['dietary_11']<>""){ $sql_where .= " OR dietary_id=11 "; } if ($_POST['dietary_12']<>""){ $sql_where .= " OR dietary_id=12 "; } if ($_POST['dietary_13']<>""){ $sql_where .= " OR dietary_id=13 "; } if ($_POST['dietary_14']<>""){ $sql_where .= " OR dietary_id=14 "; } if ($_POST['dietary_15']<>""){ $sql_where .= " OR dietary_id=15 "; } if ($_POST['dietary_16']<>""){ $sql_where .= " OR dietary_id=16 "; } if ($_POST['dietary_17']<>""){ $sql_where .= " OR dietary_id=17 "; } if ($_POST['dietary_18']<>""){ $sql_where .= " OR dietary_id=18 "; } if ($_POST['dietary_19']<>""){ $sql_where .= " OR dietary_id=19 "; } if ($_POST['dietary_20']<>""){ $sql_where .= " OR dietary_id=20 "; } $sql_where .= ") ";if ($_POST['muscle']<>"" || $_POST['performance']<>"" || $_POST['diet']<>"" || $_POST['lean']<>"" || $_POST['energy']<>"" || $_POST['health']<>""){$sql_end .= " AND feeds.product_feed_id = product_categories_map.product_id "; } if ($_POST['type_2']<>"" || $_POST['type_3']<>"" || $_POST['type_4']<>"" || $_POST['type_5']<>"" || $_POST['type_6']<>"" || $_POST['type_7']<>"" || $_POST['type_8']<>"" || $_POST['type_9']<>"" || $_POST['type_10']<>"" || $_POST['type_11']<>"" || $_POST['type_12']<>"" || $_POST['type_13']<>"" || $_POST['type_14']<>"" || $_POST['type_15']<>"" || $_POST['type_16']<>"" || $_POST['type_17']<>"" || $_POST['type_18']<>"" || $_POST['type_19']<>"" || $_POST['type_20']<>""){$sql_end .= " AND feeds.product_feed_id = type.product_id "; } if ($_POST['source_1']<>"" || $_POST['source_2']<>"" || $_POST['source_3']<>"" || $_POST['source_4']<>"" || $_POST['source_5']<>"" || $_POST['source_6']<>"" || $_POST['source_7']<>"" || $_POST['source_8']<>"" || $_POST['source_9']<>"" || $_POST['source_10']<>"" || $_POST['source_11']<>"" || $_POST['source_12']<>"" || $_POST['source_13']<>"" || $_POST['source_14']<>"" || $_POST['source_15']<>"" || $_POST['source_16']<>"" || $_POST['source_17']<>"" || $_POST['source_18']<>"" || $_POST['source_19']<>"" || $_POST['source_20']<>""){ $sql_end .= " AND feeds.product_feed_id = source.product_id "; } if ($_POST['sweeteners_1']<>"" || $_POST['sweeteners_2']<>"" || $_POST['sweeteners_3']<>"" || $_POST['sweeteners_4']<>"" || $_POST['sweeteners_5']<>"" || $_POST['sweeteners_6']<>"" || $_POST['sweeteners_7']<>"" || $_POST['sweeteners_8']<>"" || $_POST['sweeteners_9']<>"" || $_POST['sweeteners_10']<>"" || $_POST['sweeteners_11']<>"" || $_POST['sweeteners_12']<>"" || $_POST['sweeteners_13']<>"" || $_POST['sweeteners_14']<>"" || $_POST['sweeteners_15']<>"" || $_POST['sweeteners_16']<>"" || $_POST['sweeteners_17']<>"" || $_POST['sweeteners_18']<>"" || $_POST['sweeteners_19']<>"" || $_POST['sweeteners_20']<>""){ $sql_end .= " AND feeds.product_feed_id = sweeteners.product_id "; } if ($_POST['flavour_1']<>"" || $_POST['flavour_2']<>"" || $_POST['flavour_3']<>"" || $_POST['flavour_4']<>"" || $_POST['flavour_5']<>"" || $_POST['flavour_6']<>"" || $_POST['flavour_7']<>"" || $_POST['flavour_8']<>"" || $_POST['flavour_9']<>"" || $_POST['flavour_10']<>"" || $_POST['flavour_11']<>"" || $_POST['flavour_12']<>"" || $_POST['flavour_13']<>"" || $_POST['flavour_14']<>"" || $_POST['flavour_15']<>"" || $_POST['flavour_16']<>"" || $_POST['flavour_17']<>"" || $_POST['flavour_18']<>"" || $_POST['flavour_19']<>"" || $_POST['flavour_20']<>""){ $sql_end .= " AND feeds.product_feed_id = flavour.product_id "; } if ($_POST['ingredients_1']<>"" || $_POST['ingredients_2']<>"" || $_POST['ingredients_3']<>"" || $_POST['ingredients_4']<>"" || $_POST['ingredients_5']<>"" || $_POST['ingredients_6']<>"" || $_POST['ingredients_7']<>"" || $_POST['ingredients_8']<>"" || $_POST['ingredients_9']<>"" || $_POST['ingredients_10']<>"" || $_POST['ingredients_11']<>"" || $_POST['ingredients_12']<>"" || $_POST['ingredients_13']<>"" || $_POST['ingredients_14']<>"" || $_POST['ingredients_15']<>"" || $_POST['ingredients_16']<>"" || $_POST['ingredients_17']<>"" || $_POST['ingredients_18']<>"" || $_POST['ingredients_19']<>"" || $_POST['ingredients_20']<>""){ $sql_end .= " AND feeds.product_feed_id = ingredients.product_id "; } if ($_POST['dietary_1']<>"" || $_POST['dietary_2']<>"" || $_POST['dietary_3']<>"" || $_POST['dietary_4']<>"" || $_POST['dietary_5']<>"" || $_POST['dietary_6']<>"" || $_POST['dietary_7']<>"" || $_POST['dietary_8']<>"" || $_POST['dietary_9']<>"" || $_POST['dietary_10']<>"" || $_POST['dietary_11']<>"" || $_POST['dietary_12']<>"" || $_POST['dietary_13']<>"" || $_POST['dietary_14']<>"" || $_POST['dietary_15']<>"" || $_POST['dietary_16']<>"" || $_POST['dietary_17']<>"" || $_POST['dietary_18']<>"" || $_POST['dietary_19']<>"" || $_POST['dietary_20']<>""){ $sql_end .= " AND feeds.product_feed_id = dietary.product_id ";}$query=$sql_select . $sql_from . $sql_where . $sql_end . $sql_group . $sql_orderby;    $product_results=dbselect( $query,"dbLinkInt" );

Edited September 21, 2015 by davej

[davej]

You need a 10-way join? Good luck. And without join conditions I don't even know what that does.

[justsomeguy]

I've just been told that a query is too long and doesn't end

What is that supposed to mean, are you getting an error from the database? If so, what is it? Print out the final query, what does it look like? Is your dbselect function checking for database errors?That's definitely the single most inefficient way to do a join. You are joining 10 tables there. If every one of those tables only contains 5 rows, then you are asking MySQL to create a temporary table that contains 5¹⁰ = 9,765,625 rows. And that's if you only start with 5 in each table. If each table has 100 rows then you're telling it to create a temporary table with 100,000,000,000,000,000,000 rows. That's 100 quintillion rows. Unless you're the NSA, I highly doubt that your server has enough temporary storage to build a table with 100 quintillion rows which you then try to filter by applying a bunch of WHERE conditions to every row. I don't know how long you have to wait, but it's going to take some time to filter 100 quintillion rows with no indexes.You need to read about joins. Specifically, you need to design those 10 tables so that they have indexes that will make the joins faster, and instead of just listing them all separated by commas you need to explicitly join each pair and tell MySQL how to join them, not just tell it to create every possibility of rows like you're doing now. This is what a join should look like:

SELECT owner.name, owner.address, pet.name FROM owner INNER JOIN pet ON pet.owner_id = owner.id AND pet.gender='female'

Note that it explicitly lists which columns to return from which tables, that it explicitly joins the tables on the column that joins them, and it moves join conditions from the WHERE clause to the ON clause. It should only join the rows that you need, not all of them. And if you tell it to select * then hopefully that means you don't have columns in the 10 tables with the same names, like "id", "name", etc. If you do, then you need to list those and give them aliases to make them unique. Lastly, in the above example the owner table would have the id column be the primary key, and the pet table would have both the owner_id and gender columns be indexes because they are both used to join the table.In short, there's more to database design then just slapping 10 tables together with a ton of WHERE conditions and expecting MySQL to figure everything out. It's going to work a lot better if you help MySQL optimize that query by designing the tables correctly and by structuring the join correctly.

[sonicthehedgehog]

I didn't know about INNER JOIN before. I've had a read of it and have change the code to this:

$sql_from = " FROM feeds INNER JOIN product_categories_map on product_categories_map.product_id = feeds.product_feed_id INNER JOIN type on type.product_id = feeds.product_feed_id INNER JOIN source on source.product_id = feeds.product_feed_id INNER JOIN sweeteners on sweeteners.product_id = feeds.product_feed_id INNER JOIN flavour on flavour.product_id = feeds.product_feed_id INNER JOIN ingredients on ingredients.product_id = feeds.product_feed_id INNER JOIN dietary on dietary.product_id = feeds.product_feed_id INNER JOIN shipping on shipping.product_id = feeds.product_feed_id INNER JOIN merchant on merchant.product_id = feeds.product_feed_id WHERE feeds.enabled=1 and feeds.stock=1 and feeds.deleted=0 and feeds.brand_name=shipping.merchant and shipping.country_id=".dbstr( $_POST['country'] )." AND ( product_feed_id != 1 ";

Does that then mean I can get rid of this line then

$sql_end .= " AND feeds.product_feed_id = product_categories_map.product_id ";

Edited September 22, 2015 by sonicthehedgehog

[davej]

What it probably means is that you can't use this OLTP approach. It's still a 10-way join. If you really need the data from all these tables then you probably need to denormalize the data into a star schema / data mart.

 

https://www.youtube.com/watch?v=q77B-G8CA24

 

https://www.youtube.com/watch?v=cwpL-3rkRYQ&list=PLrbIyvYCdg0iAUQoxG5vI_yKqzZ2AcgGe

[sonicthehedgehog]

Sorry I don't understand what you mean? I can't get YouTube at work. Unfortunately the database was already set up so I can't change the structure of it

[davej]

What I'm saying is that complex queries such as yours are not normally run against live transactional (OLTP) databases. They are too slow and they interfere with the other users of the database. Therefore the database is copied and transformed into a denormalized (OLAP) version that can easily handle such queries.

 

https://en.wikipedia.org/wiki/Online_analytical_processing

 

https://en.wikipedia.org/wiki/Star_schema

[sonicthehedgehog]

Oh okay I see what you're saying. So something like a table that will store the info then delete it after say a day?

[davej]

There are various approaches, but basically you keep a separate semi-read-only database for the management to use for business analysis and sales forecasting.