14

How can I optimize this SQL query with ORDER BY RAND ()

 2 years ago
source link: https://www.codesd.com/item/how-can-i-optimize-this-sql-query-with-order-by-rand.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

How can I optimize this SQL query with ORDER BY RAND ()

advertisements

I'm trying to optimize this query because the DB it runs against is huge and the host says this query is causing a serious load on the server. I've read some of the other answers about how to replace ORDER BY RAND() but I don't know enough about SQL to adapt those answers to this particular query. Can anyone help? TIA

  SELECT COUNT( p.prod_id ) AS no_prod, s.*
    FROM product p, seller s
   WHERE s.admin_status = '1'
     AND s.pay_status = '1'
     AND s.sub_type != ''
     AND p.seller_id = s.seller_id
GROUP BY s.seller_id
  HAVING COUNT( p.prod_id )>5
ORDER BY RAND()
   LIMIT 0, 4

You may be better off adding a non-unique index which includes the fields seller.admin_status, seller.pay_status, seller.sub_type. You'll get the biggest bang for your buck by indexing fields referenced in your WHERE clause.

Related Articles

How can I optimize this SQL query with nested SELECT?

I have the following query: SELECT src_big, created, modified, owner, aid, caption FROM photo WHERE aid IN (SELECT aid, modified FROM album WHERE owner IN (SELECT uid2 FROM friend WHERE uid1=me() or uid2 = me())order by modified desc) ORDER BY create

How can I optimize this SQL query with a large IN clause?

I have a fairly complicated operation that I'm trying to perform with just one SQL query but I'm not sure if this would be more or less optimal than breaking it up into n queries. Basically, I have a table called "Users" full of user ids and the

How can I optimize this SQL query?

Right now I have this SQL query which is valid but always times out: SELECT ( SELECT (MAX(WP_ODOMETER) - MIN(WP_ODOMETER)) / DATEDIFF(DAY, MIN(WP_DATETIME), MAX(WP_DATETIME)) FROM WAYPOINTS WHERE WP_DATETIME BETWEEN DATEADD(DAY,-14,GETDATE()) AND GET

How can I make this sql query with jpa?

I'm trying to create a query in my entities. I have these following tables in mysql DB : Categorie : id, nom ... Article : num_code_barre_, categorie ... In my entity Categorie : ... @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Basic(opti

How can I optimize this mysql query with nested joins?

Below is my query which I need to optimize. SELECT UPPER(IFNULL(op.supplier_payment_method,s.default_payment_method)) AS Payment_Method, op.supplier_payment_date AS Payment_Date, Date_format(IFNULL(op.supplier_payment_date,op.ship_date),'%M %Y') AS P

How can I optimize this SQL outer join query?

SCENARIO I need to select records from test_userData based on a 1-to-1 match from test_userCheck on the columns customer or account_info. The code below will create a mock-up of the tables and will populate with random data for the purpose of my ques

How can I optimize the MySQL query with multiple joins?

Any inputs on how can I optimize joins in the MySQL query? For example, consider the following query SELECT E.name, A.id1, B.id2, C.id3, D.id4, E.string_comment FROM E JOIN A ON E.name = A.name AND E.string_comment = A.string_comment JOIN B ON E.name

How can I optimize the SQL query?

I have a query an SQL query as follows, can anybody suggest any optimization for this; I think most of the effort is being done for the Union operation - is there anything else can be done to get the same result ? Basically I wanna query first portio

How can I accelerate this SQL query on MySQL 4.1?

I have a SQL query that takes a very long time to run on MySQL (it takes several minutes). The query is run against a table that has over 100 million rows, so I'm not surprised it's slow. In theory, though, it should be possible to speed it up as I r

How can I rewrite this LINQ query with reflection

So I had written this LINQ query using reflection, and later found out it isn't supported. What would be the best way to get the same functionality from this code? List<Profile> profilesFromUUID = await MobileService.GetTable<Profile>().Where(

How can we improve this SQL query?

I believe this is a SQL query running on Oracle: SELECT ID, DEVICE_TYPE, S3_KEY, TO_CHAR(CREATION_DATE, 'YYYY-MM-DD HH24:MI:SS') AS CREATION_DAT FROM KASE_DDL.ARCHIVED_LOG WHERE CREATION_DATE >= TO_DATE('{DIST_YYYY/MM/DD HH24:MI:SS_UTC}', 'YYYY/MM/DD

How can I optimize this MySQL query?

I am using the following MySQL query in a PHP script on a database that contains over 300,000,000 (yes, three hundred million) rows. I know that it is extremely resource intensive and it takes ages to run this one query. Does anyone know how I can ei

How can I make this SQL query faster?

I have a table user_notifications that has 1100000 records and I have to run this below query but it takes more than 3 minutes to complete the query what can I do to improve the fetch time. SELECT `user_notifications`.`user_id` FROM `user_notificatio

How can I optimize this Linq query to find blog articles with most views in the last 24 hours

I have a blog website which regularly retrieves the view count for each blog post from Google Analytics and stores it in the database. The Blog table has a one to many relationship with the ViewStats table. The ViewStats table simple stores Date and

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK