When i query a collection of Persons from the database i want to select the list of positions each person has. I chose to map Linq Entities to a domain model so i have to build a custom entity for each position. When the query is executed i see in the profiler that for each person loaded an additional select of positions is executed le...
i have a table in database that is having 7k plus records. I have a query that searches for a particular id in that table.(id is auto-incremented) query is like this-> select id,date_time from table order by date_time DESC; this query will do all search on that 7k + data.......isn't there anyways with which i can optimize this query ...
I have two tables, one for routes and one for airports. Routes contains just over 9000 rows and I have indexed every column. Airports only 2000 rows and I have also indexed every column. When I run this query it can take up to 35 seconds to return 300 rows: SELECT routes.* , a1.name as origin_name, a2.name as destination_name FROM ro...
I'm pretty green on mysql and I need some tips on cleaning up a query. It is used in several variations through out a site. Its got some subquerys derived tables and fun going on. Heres the query: # Query_time: 2 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SELECT * FROM ( SELECT products . *, categories.category_name AS category, ...
Imagine I have a table which stores a series of sparse vectors. A sparse vector means that it stores only the nonzero values explicitly in the data structure. I could have a 1 million dimensional vector, but I only store the values for the dimensions which are nonzero. So the size is proportional to the number of nonzero entries, not ...
I have two tables, the first is a big table (millions of rows), with the most interesting column being an integer I'll just call "key." I believe this solution would be identical for date or datetime ranges as well though. The second table is much smaller (thousands of rows) with a bunch of attributes that are interesting to me which a...
Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query) I'm asking because JOINing them would complicate A LOT the design of my application If they are faster, can anyone approximate very roughly by how much? If it's 1.5x I don't care, but if...
Oracle START WITH ... CONNECT BY clause is applied before applying WHERE condition in the same query. Thus, WHERE constraints won't help optimize CONNECT BY. For example, the following query will likely perform full table scan (ignoring selectivity on dept_id): SELECT * FROM employees WHERE dept_id = 'SALE' START WITH manager_id is n...
Consider the following database tables: Table "messages" with 13,000,000 rows (one row per message). Table "users" with 3,000,000 rows (one row per user). The following query is used to fetch a bunch of messages and the corresponding users: SELECT messages.id, messages.message, users.id, users.username FROM messages INNER JOIN users...
Table has two columns: CREATE TABLE items ( k INT auto_increment PRIMARY KEY, val INT UNSIGNED ) ENGINE=MyISAM; I put four items in the table: INSERT INTO items (val) VALUES (12),(23),(45),(56); Now if I do: EXPLAIN SELECT * FROM items ORDER BY k; I get the dreaded "using filesort". What's going on? According to this p...
Hi, I got a query with five joins on some rather large tables (largest table is 10 mil. records), and I want to know if rows exists. So far I've done this to check if rows exists: SELECT TOP 1 tbl.Id FROM table tbl INNER JOIN ... ON ... = ... (x5) WHERE tbl.xxx = ... Using this query, in a stored procedure takes 22 seconds and I woul...
How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2? I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal. Edit: The answer for me turned out to be "You can't. You don't ha...
Hi I would really appreciate it if some of you could help optimize my tables, as they currently take extremely long to execute because of the following issues: Main table: game { game_id [PRIMARY KEY] *team_id *stadium_id home_score opponent_score date tour half_home_score half_opp_score attendance ref...
Here's the query: SELECT COUNT(*) AS c, MAX(`followers_count`) AS max_fc, MIN(`followers_count`) AS min_fc, MAX(`following_count`) AS max_fgc, MIN(`following_count`) AS min_fgc, SUM(`followers_count`) AS fc, SUM(`following_count`) AS fgc, MAX(`updates_count`) AS max_uc, MIN(`updates_count`) AS min_uc, SUM(`u...
There are many instances where we are not happy with the decisions that Oracle's cost-based-optimizer makes regarding the query execution plan. Using hints, less-than-straightforward query transformations, index reorganization and instance parameters we then try to coax it into doing what we think makes more sense. It is very much taking...
The query SELECT * FROM Table WHERE Path LIKE 'geo-Africa-Egypt-%' can be optimized as: SELECT * FROM Table WHERE Path >= 'geo-Africa-Egypt-' AND Path < 'geo-Africa-Egypt-zzz' But how can be this done: select * from foodDb where Food LIKE '%apples%"; how this can be optimized? ...
I have a composite index based on 3 columns, two of which are constrained in my query and the 3rd is in order by clause yet mysql doesn't use index for sorting. explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc; +----+-------------+--------+------+------------------------------...
Hi, I have on table named players, then other tables named tries, conversions, penalties, dropgoals. I need to get the player_id from players, then count the following: number of tries for player_id in tries number of conversions for player_id in conversions number of penalties for player_id in penalties number of dropgoals for player...
Its particular query pops up in the slow query log all the time for me. Any way to improve its efficiency? SELECT mov_id, mov_title, GROUP_CONCAT(DISTINCT genres.genre_name) as all_genres, mov_desc, mov_added, mov_thumb, mov_hits, mov_numvotes, mov_totalvote, mov_imdb, mov_release, ...
Hi, I have an extremely large MySQL database (about 1 million items) and am running a fairly complex query on that database. It takes about 2 seconds when I enter the SQL command into phpMyAdmin but it takes around 1-2 minutes to generate that same output using PHP code. Why would there be such a time difference? If it was a poorly ex...