First, every PK is an Index
But you can also make other fields an Index.
MySQL has an index table and it checks that to see if the field in question is there.
If it is, it “knows” where to go and doesn’t need to “look” through tables.
Making everything an Index is usually a poor idea as it bloats MySQL, but making a field that is used often an Index can speed things up considerably.
Could you post the resulting query? It’s hard to see what it looks like looking at the php code
In that case the id fields which are the most important ones are indexed but if I make the name field an index too will that help?
Unfortunately I can’t show the results at the moment because it’s sensitive information and my boss doesn’t want anybody to know about it incase her competition find out and beat her too it.
I’ll try and change it and see if I can post that.
Thank you everyone for all of your help, I’m so grateful
Not the results of the query, the query itself. Of course, if your boss says no it’s no, but I can assure you from the looks of it that you aren’t creating some top secret query that nobody has ever thought of before 
I haven’t got a lot of SQL optimising experience, but as a start you could consider indexing on fields that you select from - in my head that suggests that rather than reading through every row to see if it matches your criteria, it would be able to read them from the index on those columns and never read those that don’t contain your selections.
Isn’t there some kind of performance profiling available for MySQL that would analyse the query and suggest ways to speed it up? I vaguely recall something similar on SQL Server, but maybe I’m recalling people wishing that there was such a thing.
I might be misunderstanding your words, but an index shouldn’t be on fields being selected, but rather the fields needed to locate the data. In other words, indexes should be used for the fields behind the “WHERE” clause. Even with a compound index, which can hold up to 16 columns, the main goal is still the faster lookup and not the faster retrieval of the actual selected fields.
Also, often the index is kept in memory and that is when it is most advantageous from a performance perspective. In general though, what makes the index faster is the fact it doesn’t scan all the data (which is what you mentioned), but the index can be scanned in a certain fashion (like b-tree, r-tree or hashed). These index scanning techniques are much, much faster than a straight sequential scan of the whole table without the index.
So, I think you are thinking correctly about indexing. You just need to get to know what is going on beneath the hood of a MySQL database a bit better.
Scott
Yes, I meant the fields the OP is applying conditions to, as you said the WHERE clauses. Poor choice of words.
The host have said I should add $sql_where=substr( $sql_where,0,-4 ); in at the end of the query. I’ve done that and it now seems to work quicker but I’m not sure what it does?
if( empty( $_POST['muscle'] ) && empty( $_POST['diet'] ) && empty( $_POST['lean'] ) ) {
$_POST['muscle']=1;
}
if ($_POST['muscle']<>""){
$sql_where .= " category_id=1 OR ";
}
if ($_POST['diet']<>""){
$sql_where .= " category_id=3 OR ";
}
if ($_POST['lean']<>""){
$sql_where .= " category_id=4 OR ";
}
$sql_where=substr( $sql_where,0,-4 );
From my understanding the substr will remove the last 4 characters from $sql_where so instead of it being ’ category_id=1 OR ’ is would actually be ’ category_id=1’ ? Have I got that right? Also is there an advantage to doing it like that?
Using substr -4 will work as long as the query always ends with " OR "
If it doesn’t, IMHO it would be better to use rtrim
Advantages? You won’t get a syntax error from trailing " OR "s
If you recall from my POST #10 that I mentioned that you should use !empty() instead of comparing POST key that might not exist. Also if you put the OR at the beginning you won’t have this trim problem.
if (!empty($_POST['muscle'])){
$sql_where .= " OR category_id=1 ";
}
if (!empty($_POST['diet'])){
$sql_where .= " OR category_id=3 ";
}
if (!empty($_POST['lean')){
$sql_where .= " OR category_id=4 ";
}
Though you are querying 10 tables, you must only be using known fields from your result. The biggest time saver is going to be defining these fields in your select statement… I know this will be a big list of fields but this is where you should focus your efforts.
I was trying to figure out why I hadn’t noticed those trailing ORs in the query build-up, then I realised that they aren’t in the code you posted, they all had leading ORs. Any reason for the change? Leading makes much more sense.
Would a trailing OR include more records through an implied “always positive” condition, or just throw a syntax error?
It was the host that changed that so I thought it was best to leave it there
Sorry that was my fault I’d forgot to add that to the code
I know I’m really slow about this but I’ve just been readying about INNER JOIN and wondered if that was the best way of doing things?
I’ve changed 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 ";
and wondered if that would work? I’m reluctant to test it without checking first as if it’s wrong it’ll crash the server again and my boss is getting seriously fed up with me!
Also if the above it correct can I remove this line then:
$sql_end .= " AND feeds.product_feed_id = product_categories_map.product_id ";
Looks llike you are missing the table name feeds in the WHERE condition.
( feeds.product_feed_id != 1 ";
I am not an expert on join types. I use LEFT JOIN for just about every instance where I am joining tables as I want all records from the first table and IF there are matches in subsequent tables, get those records. I believe INNER JOIN is going to return records only IF there are matches on all join instances.
Have you made any progress defining the called fields?
Do I need to add the table names if the fields are unique to that table? I think you’re right though, it sounds like LEFT JOIN might be better. I tried to only call a field if it’s needed but for the main table (feeds) I need every field unfortunately which is one of the reasons I decided to use some kind of join
Yes, I would add the table name to all where conditions.
Also, if you do call specific fields from tables (which I think will improve things a lot) you should use table names with the fields.
Note: If you haven’t already I would make a test page for testing your query on rather than changing the final page. De-bug it, improve it, Print result array to screen, check time it takes. Work on calling only fields needed on the page. Then edit your page when you are happy with it. BTW, I did PM you on sending me table structures and needed fields if you needed help.
Thank you, I’ll add the table names in - it’s definitely getting there so I’m very happy now!
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.