0
votes
0answers
72 views

How to know the correct indexes for mysql?

I tried to find out with explain where I must set the correct index. A current select takes about 20 seconds. Can someone tell me which would be the correct index for my tables? SELECT trade.*, ...
0
votes
1answer
27 views

Optimizing a query so MySQL will use the indexes for the joins

Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL. Wrote a query but it's very slow. Here's the query: SELECT Table1.*, (long list of fields for each ...
1
vote
0answers
116 views

MySQL join query indexes

After recently discovering the MySQL feature to log queries that aren't using indexes, I've been chasing these down in a large app, looking for places to improve performance. There is at least one ...
0
votes
0answers
305 views

MySql INNER JOIN doesn't use composite index

I have two tables, table A which contains date and name of table B entry and table B which contains its name and type. Table a has a composite index on table_b_name and date. I'm selecting all table A ...
2
votes
2answers
2k views

MySQL not using indexes when joining against another table

I have two tables, the first table contains all articles / blog posts within a CMS. Some of these articles may also appear in a magazine, in which case they have a foreign key relationship with ...
1
vote
1answer
120 views

Combining multiple left join for one single line

We have a query as below. SELECT tblSData.header, tblEAlert.eMessage FROM tblSData LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID Where tblSData.aID=".$aID." Order By tblSData.dateTimer ...
1
vote
0answers
35 views

parenthetic grouping of join clauses

What is the difference between these joins? a left join b left join c (a left join b) left join c a left join (b left join c) Does the grouping only affect the order of the joins? Will the query ...
0
votes
1answer
515 views

Group By on column from joined table

its about the following query. EXPLAIN SELECT phrase, infinitiv FROM `dict_verbs_all` `t` INNER JOIN `dict_verbs_all_language` `verbsGerman_c` ON (`t`.`id`=`verbsGerman_c`.`verb_id`) INNER JOIN ...
3
votes
1answer
3k views

how to fetch different data on compare rows of two tables

I have two tables in my database, one is user_app table in which user info are stored, table are as: +--------------+--------------+------+-----+-------------------+-----------------------------+ | ...
2
votes
1answer
184 views

MySQL: Very slow query, not using index, no joins involved

I am running what I think is a very simple query: SELECT SQL_NO_CACHE m.mbr_nbr FROM tran.member m WHERE m.cardnbr = 000099999930 The table member is a poorly written one, with 133 columns and ...
1
vote
2answers
2k views

Improving performance of nasty nested-view joins

I have a moderate-sized database spread out over a few tables, the rough architecture is: Input Data (Data ID, Session ID and a few fields of statistical importance) Input File (Data ID and a blob) ...
4
votes
2answers
1k views

Index on primary key not used in simple join

I have the following table and index definitions: CREATE TABLE munkalap ( munkalap_id serial PRIMARY KEY, ... ); CREATE TABLE munkalap_lepes ( munkalap_lepes_id serial PRIMARY KEY, ...