mysql Questions

Count GROUP_CONCAT column to check for duplicates – Mysql

April 12, 2023
SolarNewb
3 Answers

I have the following query: SELECT cpe.entity_id AS configurable_product_id, cpe.sku AS configurable_product_sku, GROUP_CONCAT(DISTINCT cpsa.attribute_id ORDER BY cpsa.attribute_id SEPARATOR ',') AS configurable_super_attribute_ids, cp.entity_id AS simple_product_id, cp.sku AS simple_product_sku, GROUP_CONCAT(cpei.attribute_id, '_', cpei.value ORDER BY cpei.attribute_id SEPARATOR ',') AS simple_product_super_attribute_values FROM catalog_product_entity AS…

VIEW QUESTION

Correcting SQL using SET in MySQL to display

April 11, 2023
Chon A
2 Answers

ITrying to get 2 separate select statements to show in one result set using a user defined variable. Should I use a Union or With somewhere here? SET @X1 = ((SELECT SUM(ef.Fee * (rd.ActualReturn - ra.startdate)) as "total revenue from…

VIEW QUESTION

How add 1billion rows – Mysql

April 11, 2023
faradei
2 Answers

I have a table users (username, email, validts, confirmed. I want add 1 billion unique rows I tried to do this but it only adds 16 unique values INSERT INTO users (username, email, validts, confirmed) SELECT CONCAT('user', n) AS username,…

VIEW QUESTION

Flag the Match type of a query in results without running subquery twice – Mysql

April 11, 2023
Russell Van Curen
2 Answers

I have a complex query. The following below is the most basic example of what I want to do. SELECT match_by_color AS match_type, t1.* FROM t1 WHERE ( t1.color LIKE 'color%') -- match_by_color OR (t1.type in (select type from t2))…

VIEW QUESTION

Applying DISTINCT to multiple columns? – Mysql

April 11, 2023
Katsu
2 Answers

Lets say I have a table of: order_id, agent_name, order_amount. order_id agent_name order_amount 1 Justin 500 2 Justin 500 3 Justin 1000 4 Justin 1000 5 Pablo 750 6 Dinesh 500 How would I write a query to apply DISTINCT…

VIEW QUESTION

Want distinct list of ids in mysql variable

April 11, 2023
hetali
2 Answers

Table |id |user_id | req_user_id | |---|--------|-------------| |1 | 1 | 2 | |2 | 2 | 9 | |3 | 7 | 2 | When I search with user_id or req_user_id = 2 output should be (1,9,7,2) tried SET…

VIEW QUESTION

MySQL 64kb size limit for MyISAM table-based metadata storage unclear – differences between MySQL and MariaDB

April 11, 2023
Deckard
2 Answers

The information found in the official MySQL documentation at https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html does not match the information shown in the manual at https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html for version 8. First link says that the 64kb limit was dropped, the 2nd link says it exists. It…

VIEW QUESTION

Log queries executed on a UI tool like phpMyAdmin – Mysql

April 11, 2023
Vishal Dubey
2 Answers

I am trying to populate a history table in my database whenever information_schema.PROCESSLIST gets updated. I tried: CREATE TRIGGER populate_history AFTER INSERT ON information_schema.PROCESSLIST FOR EACH ROW INSERT INTO myDatabase.history_changes (host, db, query) VALUES (new.HOST, new.DB,new.INFO); But I am getting…

VIEW QUESTION

MySQL – Doing addition from "subquery field" in SELECT using alias

April 11, 2023
DenjanD
2 Answers

I would like to add some SUMs from subqueries in the SELECT into another new field. Here is the query that I am currently using: SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date, (SELECT SUM(qty) AS product1_shift1 FROM bill_details JOIN…

VIEW QUESTION

Does mysql slow down because there are too many columns to query?

April 11, 2023
johnson
2 Answers

I have a question, does the database cluster index store the whole row of data? So if you have a lot of columns, the index is still looking at the whole row of data. So does having more columns in…

VIEW QUESTION