Tagged Questions
0
votes
2answers
47 views
How do I use subquery on the same table in MySQL?
I have a query like this which takes a really long time to run. The table is around 4 million rows.
DELETE FROM TABLE WHERE value_was IS NULL OR value_was <= value_now;
I'm hoping I could ...
0
votes
2answers
49 views
How to use and optimize subquery on 100 million rows
What I'm trying to do is running a job on more than 100 million domains which haven't processed before.
I have two tables, "domain" and "domain_setting", on every batch (10.000 domains per batch) I'm ...
2
votes
1answer
62 views
Best practice: Unions or a derived table?
I've inherited a medium-sized database with a terrible schema. The sanitized portion in question is like so:
CREATE TABLE `pending` (
...
`invoice` int(11) DEFAULT NULL,
`lid` int(11) DEFAULT ...
0
votes
3answers
42 views
Not getting the expected result with 'IN' and subquery
I am trying to access the ring_to_number from tbl_destinations that holds all the numbers. And field destinations has the comma separated ID like 1,3. but according to this query i just get the the ...
1
vote
1answer
28 views
Identical subquery optimisation in an update
update activitybooking set `submitted`='1' where id='958'
and (select SUM(pool1_count) from (select pool1_count from `activitybooking` where `abt`='12' and
(id='958' or ...
0
votes
1answer
96 views
MySQL subqueries that use range based on values of main queries don't use indices properly
I think I've isolated a problem that has been affecting many of my queries lately.
And would like some help to figure out a solution for this.
Ok so my findings are that a normal query that runs very ...
2
votes
1answer
114 views
Selecting minimum value using a subquery
I need to write a query to grab the lowest price from each merchant and output the price link (p_link) and some other information such as merchant name + rating.
We have a table for prices ...
1
vote
1answer
65 views
Why is this query so slow?
I have a quiz site with approx 2000 questions. The function in question worked great when I wasn't tracking that many users, but now there are 300,000 question that users have seen. The function only ...
1
vote
2answers
219 views
SELECTing multiple columns through a subquery
I am trying to SELECT 2 columns from the subquery in the following query, but unable to do so. Tried creating alias table, but still couldn't get them.
SELECT DISTINCT petid, userid,
(SELECT ...
1
vote
1answer
364 views
Select multiple columns in subquery
I am having trouble selecting all cloumns in a sub query and can't seem to get no where with joining for replacement.
http://sqlfiddle.com/#!2/2481e/1
The link is to the schema with my current ...
2
votes
1answer
146 views
Does MySQL have problems with nested insert like with subqueries in where?
Query 1:
INSERT `personal`.`locations`
SELECT DISTINCT `s`.*
FROM `references` `t`
JOIN `locations` `s` ON `first_id` = `s`.`id`
WHERE
`lat` >= 37.3
AND `lat` <= ...
3
votes
1answer
332 views
Subqueries run very fast individually, but when joined are very slow
ypercube solved the problem. Subqueries were totally unnecessary, and the whole thing works with plain joins. It is still strange that MySQL's optimizer could not make use of my original query, ...
2
votes
2answers
106 views
Query taking too much time
My query is taking too much time to execute. Please help me here.
mysql> explain
select pcm.catalog_id
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c
...
1
vote
1answer
177 views
How to re-arrange rows to best fit within groups?
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order id num_items cat order_in_cat cumulative_sum folder
1 ...
1
vote
1answer
210 views
Getting max values from MySQL tables
I have three tables:
competitions (id, name, date)
athletes (id,name)
results (place, id_athlete, id_competition, ranking_points)
where:
results.id_athlet=athlet.id
...
0
votes
0answers
99 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
1
vote
1answer
48 views
Query from database
I have a table named mip. The table has primary key id, a foreign key product_id and a column usage_type. product_id is a primary key id to a table named product.
It is one to many relationship from ...
3
votes
1answer
807 views
SELECT LIMIT 1 per column value?
Lets say I have the following table
-----------------------------
| user_id | comment |
-----------------------------
| 2 | thats cool |
| 2 | awesome |
| 3 | ...
3
votes
1answer
2k views
MySQL: Optimize UNION with “ORDER BY” in inner queries
I just set up a logging system which consists of multiple tables with the same layout.
There is one table for each data source.
For the log viewer, I want to
UNION all the log tables,
filter them ...
3
votes
2answers
154 views
Do MySQL sub-queries essentially use as much overhead as separate queries?
If I have a query like this:
SELECT name FROM
(
SELECT * FROM `table`
WHERE id IN (1,40,300)
ORDER BY AnotherColumn
)
From a bandwidth and overhead standpoint, is that the same thing as doing ...
5
votes
1answer
269 views
Mysql sub query returns more than 1 row
SELECT * FROM wp_posts WHERE ID IN
(
(SELECT courses FROM wp_category WHERE CatID =401) OR
(SELECT meta_value FROM wp_postmeta WHERE post_id IN (SELECT courses FROM wp_category WHERE CatID =401) ...
3
votes
2answers
999 views
How to GROUP_CONCAT DISTINCT values in a MySQL query that gets number of records and min/max values?
I'm running MySQL 5.0.88 (Coldfusion8)
I have a product search which I'm querying number-of-results as well as min/max prices/rebates across the product table. I also want to include a string of ...
0
votes
0answers
101 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
4
votes
2answers
532 views
Is there a way to hint to query optimizer to MySQL which constraints should be done first?
This is my current query:
SELECT BusinessID as ID,
111151.29341326*SQRT(pow(-6.186751-X(LatLong),2)+pow(106.772835-Y(LatLong),2)*0.98838574205337) AS Distance from
(
SELECT *
FROM
...
2
votes
1answer
69 views
Merge queries into one
The following 2 queries are working as expected and they show the count.
Is it possible to merge them into single query?
I need sum_hits columns to be part of the rest of the column shown in the ...
1
vote
2answers
1k views
MySQL: Delete all but last N records
Consider the following table:
mysql> DESCRIBE pixels;
+---------------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default ...
3
votes
3answers
3k views
How to select/count rows where the same value exists in a column but not in other column all at the same table
I have this table structure and data:
CREATE TABLE IF NOT EXISTS `default_relations_users` (
`id_user_rq` int(11) NOT NULL,
`id_user_ap` int(11) NOT NULL,
UNIQUE KEY `rusers_rq_ap_idx` ...
3
votes
3answers
210 views
How can I select using the same fields from an undetermined number of tables using MySQL?
I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:
foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
...
1
vote
2answers
113 views
How to restructure this slow query
For the following query:
SELECT l.*
FROM `leads` AS `l`
INNER JOIN `users` AS `u` ON l.client_id = u.id
WHERE (`l`.`lender_id` = 1)
AND (l.claimed_date IS NULL)
AND (`l`.`disabled` = 0)
AND ...
3
votes
2answers
203 views
MySQL cache is not getting hit by subqueries
I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.
Let's say I have an item table
ID | Item
-----------
1 | Item_1
2 | Item_2
3 | ...
3
votes
1answer
2k views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
5
votes
4answers
971 views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
3
votes
1answer
2k views
Query performance with subquery and IN clause
I am trying to select a range of data for multiple devices (unique serial numbers) from a historical table and was wondering why there is such a big difference in time for the following queries:
...
