New answers tagged

0

Depending on the distribution of data, it may be faster to run 3 queries and combine the results: ( SELECT ... WHERE field_id_56 LIKE 'AB%' ... ) UNION ALL ( SELECT ... WHERE field_id_56 LIKE 'G%' ... ) UNION ALL ( SELECT ... WHERE field_id_56 LIKE 'WO%' ... ) and have INDEX(channel_id, field_id_56) -- in that order (I am assuming that those two ...


0

why you need use subqueries ? You can use joins for get data SELECT u.name AS user, d.name AS departement, m.year FROM main AS m INNER JOIN department AS d ON m.departement_id = d.id INNER JOIN users AS u ON m.user_id = u.id ; I created a sample fidle http://sqlfiddle.com/#!9/74a4e2/1


2

I'm not sure why you need to join all three tables every time. For your specific example, what about the following query: WITH rel AS ( SELECT prm.power_relation_id FROM power_relation_members prm JOIN power_lines pl ON prm.member_id = pl.id WHERE pl.geom = :BIND_VAR_HERE -- in this case, 'abc' GROUP BY prm.power_relation_id ) SELECT pl.id, pl....


0

What's to keep that subquery from returning 0 rows or more than 1 row? Doesn't it need ORDER BY ... LIMIT 1? Use JOIN, not a subquery like you are doing. That should help performance. Temp tables are MEMORY by default. Don't try to force it; the query could crash because of max_heap_table_size.


0

I'm assuming that G followed by anything is valid in the second case. If so you sould be able to replace your 3 queries with something like: SELECT d.field_id_105 AS code , t.title AS title, , t.entry_date AS entry_date , t.entry_id AS entry_id , i.title AS installer , i.entry_id AS installer_id , d.entry_id FROM ...


0

g.dataset_id = 3 is killing the left try this FROM markers JOIN genotypes g ON g.marker_id = markers.id AND g.dataset_id = 3 The where is killing the left joins in the exists so you can drop that This may give you better performance and exists ( SELECT 1 FROM genotypes join accession ON genotypes.accession_id = accession....


0

A likely reason is that the subquery cant't be optimized using materialization. We have run into this a few times, and usually use temporary MEMORY tables as a workaround.


1

I'm not an expert on mysql/mariadb, but I suspect that it is running the sub-query one for every row in CLD_DB.lnmntr, or at least every row it needs to until it finds 500 that match the condition (which might just be 500 or might be far more depending on your data). Some SQL engines are bright enough to see that the inner query is invariant within the ...


0

You should denormalize the tables as such a normalization is leading to too many inner joins and multiple nested select statements. If normalization is very necessary try maintaining another table. As the number of images is low (~700), you might just skip the image_to_tag table. Otherwise, if the number of tags per image is very low (3-4), try keeping ...


0

FULLTEXT needs space to be space, not a "letter". That is, you cannot have spaces in a "word". I would write application code to ignore 'short' words (such as 'g') and end up with WHERE MATCH(alternate_name, product_desc, keywords) AGAINST ('+moto +plus'; IN BOOLEAN MODE) AND ( alternate_name LIKE '%moto g plus%' OR product_desc LIKE '%...


1

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id). ...for a start. You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the ...



Top 50 recent answers are included