Optimize mysql query

mrerotic · #1 10-20-2009, 09:45 PM

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?

Code:

// GET STUDIOS INFO //
$getstudios = func_query("SELECT DISTINCT value AS studioname FROM $sql_tbl[extra_field_values] WHERE fieldid='4' AND value IS NOT NULL ORDER BY RAND() LIMIT 20");

rogue · #2 10-21-2009, 04:45 AM

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.

mrerotic · #3 10-21-2009, 05:02 PM

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.

BritSteve · #4 10-22-2009, 05:21 AM

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

rogue · #5 10-22-2009, 05:27 AM

The best way to do this involves some PHP code.

mrerotic · #6 10-22-2009, 10:37 AM

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

Thanks guys - Kyle

exsecror · #7 10-22-2009, 11:01 AM

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.

BritSteve · #8 10-22-2009, 11:45 AM

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

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

rogue · #9 10-23-2009, 05:00 AM

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

exsecror · #10 10-27-2009, 02:59 AM

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)