Optimize mysql query
 

Is there anyway to make this query faster? When I have benchmarking enabled I can see that this code takes 3.40 seconds to generate. I have 100,000 products with this extra_field. Can anyone please help?

Re: Optimize mysql query
 

I'll wager that all 100,000 rows are being selected, then sorted then 20 are re-selected.

Try taking the 'order by' out of the select and see if that makes a difference.

I'm not sure what you can do if this is indeed the case.

Re: Optimize mysql query
 

Yes, this is the case. I have it selecting all products that have a fieldid ='4' but as soon as I add "ORDER BY" OR "RAND()" to this query benchmark shows this query gets very heavy. I can't seem to find another way to randomly select 20 results and have them being displayed ASC.

If anyone has any solution - PLEASE - let me know.

Re: Optimize mysql query
 

What is the ratio of rows that satisfy the condition? I mean how many of the 100,000 rows have a fieldid of 4 and are not null. Is there a primary key, like ID that is numeric?

I agree that the SQL is getting the relevant rows, adding the rand and then sorting them, probably using a temp table.

There are other ways you can try, but it will involve some PHP coding.

Steve

Re: Optimize mysql query
 

The best way to do this involves some PHP code.

Re: Optimize mysql query
 

Well Im down to try whatever if you can assist please.

Thanks guys - Kyle

Re: Optimize mysql query
 

Run the EXPLAIN query analyzer and make sure it's hitting appropriate indexes, if not make the necessary indexes to speed it up. Also @rogue using PHP to handle an SQL programming issue is not generally a great idea, many web apps fail in performance because people use a language for something SQL can do a lot faster when the query and tables are properly designed. I had to fix a lot of x-cart bottlenecks because of that.

Re: Optimize mysql query
 

Order by rand() is notorious for being slow on large tables.

http://www.webtrenches.com/post.cfm/avoid-rand-in-mysql

Re: Optimize mysql query
 

try this select:

select distinct value as studioname, rand() as random from
$sql_tbl[extra_field_values] WHERE fieldid='4' AND value IS NOT NULL ORDER BY random LIMIT 20

Re: Optimize mysql query
 

The problem with that query is that RAND() is very poor on MySQL and LIMIT 20 only works AFTER MySQL has pulled down the entire table (due to the bad coding of their query analyzer on MySQL version prior to 6)

Re: Optimize mysql query
 

so what can I do?