Slow Select Using Count(distinct) In A Table Bigger Than 100000 Records
Recently I started using MYSQL in my enterprise. I made a table which has around 100000 records. The problems is that it is really slow.. Im trying to do a query in which I get the number of distinct users per day.
This is my query:
select date(startedDate) as mydate, count(distinct(Users)) as users from Mytable
group by mydate
It is really simple and it does it correctly but it takes one minute.. One minute is not too much time but i need to insert around 10 000 000 records and thats what worries me.....
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Slow Select Count(*)
I have a table with 75,000 rows. An Explain tablename shows this 'TourPartsID', 'int(10) unsigned', 'NO', 'PRI', '', 'auto_increment' 'TourPartID', 'int(10) unsigned', 'NO', 'MUL', '', '' 'TourPartTypeID', 'int(10) unsigned', 'NO', 'MUL', '0', '' 'Language', 'int(10) unsigned', 'NO', '', '0', '' 'XML', 'mediumblob', 'NO', '', '', '' When I do a Select count(*) from table, it takes about 12secs to return my number. This is occuring in the QueryBrowser and from the .NET library when it calls the Stored Procedure that does the Select count(*).... My only quess that is my last column is a MEDBLOB but since I am NOT indexing that, I would figure MySQL ignores it.
View Replies !
View Related
Select Distinct Records
I am trying to compare the date part of a datetime value field with today's date.... Here's the sql: mySQL = "Select * from Test WHERE TheDate LIKE'"&date()"' ORDER BY TheDate" Set rs= Con.Execute( mySQL ) That return nothing even though Test has records for today..
View Replies !
View Related
Row Count Mismatch In Select Count(*) And Explain Select Count(*) From Table
mysql> explain select * from parameter; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | parameter | ALL | NULL | NULL | NULL | NULL | 3354 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from parameter; +----------+ | count(*) | +----------+ | 97 | +----------+ 1 row in set (0.00 sec)
View Replies !
View Related
Table Gets Slow At Around 60k Records
I've a table that starts getting slow with count(val) and select ... order by when it has around 60k records. Table type is innodb Strange enough, at 50k records the response time is great, but looks like it grows exponentially after that. Its something like 50k records - 0.2 seconds for count(val) 60k records - 3 seconds 100k records - 20 seconds I realize order by and count forces mysql to go through the entire table...
View Replies !
View Related
Using Count From Distinct Table...
select count(*) from tblproducts a,tblsubcategory b where (b.stSubCategoryName like '%".$keyword."%' or b.stSubCategoryDesc like '%".$keyword."%' or a.stProductName like '%".$keyword."%' or a.stProductDesc like '%".$keyword."%') and a.inSubCategoryId=b.inSubCategoryId in above query i want to count only tblproducts i used count(a.*) not worked
View Replies !
View Related
815,000+ Records, DB Table Query Too Slow
I have a database table with about 815,000 records in it, each record containing the source of individual webpages (with all the tags and multiple spacing stripped out leaving just the words). At first my match boolean queries were fine but once I got to around 100,000+ records it became too slow to use for a web-based search tool. I was able to separate the data into 10 tables and then use AJAX to run the match query on all 10 tables at the same time and that has increased performance. My question is, is it possible for me to increase the performance for the 1 table some how instead of resorting to separating the data into multiple tables? Is MySQL the right DB for this kind of setup, would MSSQL or PostgreSQL perform better for larger sets of data? I'm not sure how MySQL 5 clustering works,
View Replies !
View Related
Select / Count Not Showing Records ... Permissions?
I imported 600,000 records from SQL Server 2005 to mySQL. I know they are in there -- I just can't see them! When I do a SELECT, it processes for a while, and then returns nothing. Same with a count, it sits there processing, and then returns a '0' However when I do the same on a table I know has no rows, it definitely comes back right away. I'm logged in as root. Do I need to assign myself some permissions to see these records? And the count numbers?
View Replies !
View Related
Slow SELECT Query INNODB Table
I have a couple hundred connections doing "SELECT [Char36 Field], [LongLong Field], [Long Field] FROM [Connection Specific Table] WHERE [NonIndexed VarChar36 Field]=[Value]". Notice that the table the select statement is being called on is unique to each connection. These tables have less than 10000 records, but this statement can sometimes take over an hour to execute. It is an INNODB table.
View Replies !
View Related
Bigger Table Or More Tables
I run a site where users can send private messages to each other, like a community. There are a few thousand active users and the amount of messages per now is about 35.000.000 in total. We are upgrading to a new system and will be redesigning the message-function for better performance. Estimated new messages per year is 5-8.000.000. What is the recommended design for performance and reliability. One big fat table (InnoDB) with millions of messages, or; several tables with equal structure, each containing less data, combined with UNION for a particular user each time data is needed? With UNION, MySQL would need to search through all tables each time a message is viewed, is this an OK setup with performance in mind for a system with a few thousand (1500-2000) concurrent online users?
View Replies !
View Related
SELECT DISTINCT On A Varchar Column (big Table)
I have a table with 10 million rows in it. Now, I need to export a list of unique values from a varchar column. Normally I would do: SELECT DISTINCT MyColumn INTO OUTFILE '/dumpfile.txt' FROM MyTable But this is VERY time consuming and slows my PC down. Can you give me an advice about what should I attempt to make it last the less? For instance, should I create an index on MyColumn AND THEN execute the SELECT DISTINCT? I may look silly, but I also tried using a Memory table thinking I would cut the indexing computation time but my RAM is not big enough to contain all the rows.
View Replies !
View Related
2 Table SELECT DISTINCT Statement Not Working
I have 2 tables: 1. plus 2. not_plus Both have a field in common: region And all the other fields are different. What I need to do is distinctly select all of the different regions from both tables and then display them in a drop down menu. HTML and the PHP is easy and the dropdown is there
View Replies !
View Related
Bigger Table Size With Less Data?
I'm trying to change a table definition by dropping 2 columns from it because of normalization of data, also 1 index has to go away. The original .MYD file is 1.2Gb and the MYI 1.0Gb, the are about 20million records on it. First, I tryed the ALTER TABLE route, but it were more than 2 hours later and still was going on... I checked the directory where the files are stored and the mysqloral MYD file was reaching 1.6Gb, so I stopped it. Next I created a new table with the right schema, and issued a INSERT...SELECT from the original table, now the new MYD file is 1.7Gb and keeps growing. Would the source table be corrupt? or is it normal behavior? .....
View Replies !
View Related
Select Count Of Data Appearing In One Table From Another Table
I have three tables: t_Products (id, name) t_Shop (id, location, name) t_Carries (product, shop) If Shop carries a product, there will be a value pair in t_Carries but otherwise no record is listed. Is it therefore possible to return a list in MySQL showing something like this shop.Id, product.Id, count(or something) 1 1 0 1 2 1 1 3 1 1 4 0 Or must I use two query and programmatically generate the list?
View Replies !
View Related
Select Distinct And Include Non-distinct Columns
I have a publication table that tracks the products assigned to various publications. I want to select all of the distinct products, based on product_ID, assigned to a specific publication but I also want to return additional columns that do not need to be distinct. If I use the following select: Select distinct publication.product_ID, publication_ID.code, publication.region from publication where publication_ID = '12' I would get a list showing the three columns that I want to see which are product_ID, publication_ID and region for publication 12 but I get too many items as the distinct function means that all three of these columns must be distinct and I only need the product_ID to be distinct. So how do I find all of the distinct product_ID but also show other columns such as region? Do I need to do this with some kind of self-join?
View Replies !
View Related
SELECT DISTINCT, (and Display Other Fields Not Distinct.)
I am using SELECT DISTINCT to select 1 of a duplicted field. So far I have; SELECT DISTINCT `field1`, `field2`, `field3` FROM table1 This returns what I need. There is also another field (field4) which I also want to select, but not distinctly. Something like: SELECT DISTINCT `field1`, `field2`, `field3` NOT_DISTINCT `field4` FROM `table1` The field that is not being selected distinctly contains a '1' or a '0'. My table is ordered by field4 (0 first) does this mean the select distinct will select those with '0' before those with '1' (I want '0' to have priority when select distinct) I will only be using this SQL query once to remove duplicates from a database, I am not concerned about performance issues which someone has mentioned to me. How can I display this not-wanted-distinct field in a distinct query?
View Replies !
View Related
SELECT COUNT (*) FROM Table
Newbie: SELECT COUNT (*) FROM table When I run the above query from the command line, it works fine. When I run it from my powerbasic program, I can't seem to get a reasonsible result. What type of result does count return? I get something that looks like this: [ CHR$(133) ][ CHR$(21) ][ CH... and the type is given as -5... What is this type?
View Replies !
View Related
Two Table COUNT/select
I need to count the total (using SUM) from one column in one table, where the a condition is set from another table. so far I've been striking out with this: Code: SELECT maxbid SUM(maxbid) FROM `probid_auctions` WHERE probid_categories.parent = 1853
View Replies !
View Related
Select Count (*) From Dabase.table
I have a problem with MySQL, I am trying to execute a query similar that: select count (*) from database.table but the server return this error: "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ..." The query looks right, and if I try to execute the manuals example fails too. can anyone help me? In some place I read that exists a bug with some MySQL server versions, but I have try with several 5.0.27, 5.0.67, and some more. My server is running over Windows XP.
View Replies !
View Related
Select From Table And Count Results In Another
I have to tables table one containing airports and table two containg bookings. What I need to be able to do is count how many rows are returned form the booking table that matches each airport in the airport table. So I might have in my airport table Newcastle Heathrow Standstead etc etc and in my bookings table I have Newcastle Newcastle Standstead Heathrow Heathrow And what I want returned is Newcastle(2) Standstead(1) Heathrow(3) How can I go about doing this.
View Replies !
View Related
Best Way To Select Records WITHOUT Relationship In Another Table?
I have a stock table and a stock_category_r table which is a relationship table between stock items (products) and stock categories (M:M relationship) I need to select stock that is uncategorised. I.e Items that have not been related to ANY categories. I was using this query with a subquery: SELECT s.ID, s.code, s.name FROM `stock` s WHERE s.ID NOT IN (SELECT stockID FROM stock_category_r) Just wondering if this was the most effecient way to find stock records that have no corresponding relationship in stock_catgegory_r ? Do sub queries effect performance much? My tables for this app have very few rows, but I'm interested in the best-practice theory The table structure: CREATE TABLE `stock` ( `ID` smallint(5) unsigned NOT NULL auto_increment, `code` varchar(16) NOT NULL default '', `name` varchar(40) NOT NULL default '', `description` mediumtext NOT NULL, `image` varchar(35) NOT NULL default '', PRIMARY KEY (`ID`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `stock_category_r` ( `stockID` smallint(5) unsigned NOT NULL default Ɔ', `categoryID` tinyint(3) unsigned NOT NULL default Ɔ', PRIMARY KEY (`stockID`,`categoryID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
View Replies !
View Related
Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'. About half the rows are 'yes' and half are 'no'. Table is indexed on 'stamped'. We just need the count on 'yes'. SELECT count(*) from T where stamped='yes' or <>'no' or >'n'... is very slow. Is there a better approach / query to get a fast result?
View Replies !
View Related
Distinct Count
I have a script automatically create a new table at the beginning of every month, for the current month. The tables all have the same exact columns, but a different name (i.e. stats_October_2006, stats_November_2006). I did this just to keep the amount of records in one table as minimal as possible.I would like to be able to perform a count query across both tables and have the values merge, so it would be like performing the query on one large table. This is the query I am able to run on just one of the tables: SELECT DISTINCT domain, count( * ) AS count FROM stats_October_2006 GROUP BY domain ORDER BY count DESC However, I would like to perform the same query on multiple tables as if they were all one table. How would I go about doing this? I have looked into LEFT JOIN but don't quite understand it.
View Replies !
View Related
Distinct & Count
I have a table called users_favourite_shows which contains userid & showid, telling me whether somebody likes a show. SELECT DISTINCT(showid) FROM users_favourite_shows WHERE userid IN (3,8) which gets the shows some people like, I use distinct because I don't want the same shows coming back multiple times. My problem is trying to also get back how many people, obviously limited to 2 in the example query, like the show as well - how many times a showid appears basically, but with the DISTINCT and all I am not sure how to do this.
View Replies !
View Related
COUNT DISTINCT
I have a table that stores information on visits to various business locations. In the table I have the user_id and the office_id. How can I count how many distinct users have visited all locations. I have tried the following but it groups it by office ID and I just want one total figure... SELECT COUNT(DISTINCT(User_ID)) FROM My_Table GROUP BY Office_ID;
View Replies !
View Related
Count Distinct Columns
Hi trying to find the number of duplicate tuples/records in table based on multiple columns ie. select count(distinct list multiple column key ) from x where date = y HAVING ( COUNT(multiple column key) > 1 ) doesn't seem to work....any tips?
View Replies !
View Related
UNION And Count(DISTINCT)
I have a bunch of tables that store messages sent by users of my system. All of the tables are identical in structure and include a SentDate and a UserID. I have been asked to create a query that gives a count of active users within a specific time period, an active user being a user who sent a message in the given time period. If it were one table, then easy; SELECT count(DISTINCT(UserID)) FROM Messages1 WHERE SentDate>=FROM and SentDate<=TO Is it possible to get a count of distinct users across many (identical) tables? I can't see how to do it with UNION...
View Replies !
View Related
Getting A Count Of Distinct Elements
If I have a table like this: +----+---------------+-------------+ | id | A | B | +----+---------------+-------------+ | 1 | 1 | -1040187392 | | 2 | 1 | -1040187392 | | 3 | 2 | 1409286144 | | 4 | 2 | 1409286144 | | 5 | 2 | 1409286144 | | 6 | 2 | 1442840576 | | 7 | 3 | 989855744 | | 8 | 3 | 2080374784 | | 9 | 3 | 989855744 | | 10 | 4 | -671088640 | | 11 | 4 | 1107296256 | | 12 | 4 | 1107296256 | | 13 | 4 | 1073741824 | | 14 | 4 | 1073741824 | | 15 | 4 | 1107296256 | +----+---------------+-------------+ I want to get a frequency count of the elements. Can I do it with mysql? I mean, I want something like the following: +---------------+-------------+---------+ | A | B |frequency| +---------------+-------------+---------+ | 1 | -1040187392 | 2| | 2 | 1409286144 | 3| | 2 | 1442840576 | 1| +---------------+-------------+---------+
View Replies !
View Related
Trying To Count Distinct Users
I've recently got MySQL on an open source LAMP-server driven solution to handle PC bookings at the library I work at. Just getting my head around the differences with MS SQL and here's one that I'm stuck on (although it very likely has nothing to do with MSSQL vs. MySQL and more with my general ineptitude!) There are two columns (cardnumber & bookingtime) that I want to grab data from out of a table called "PC Bookings" in order to get a report on how many unique PC users are on the system each day. So far I've got the following: select count (distinct cardnumber), (select from_unixtime(bookingtime)) as "time" from pc_bookings group by time this is just giving me a count of 1 for each time a card number is used, not each time a unique card number is used.
View Replies !
View Related
Count Distinct Rows
If I have a table like this: col1 col2 1 100 2 100 3 300 4 200 5 100 And run a query like this (dont know if it is right syntax): SELECT DISTINCT(col2) * FROM Table I would get something like this: 1 100 3 300 4 200 But I would like to get the count of each distinct row like this: 1 100 3 3 300 1 4 200 1
View Replies !
View Related
COUNT(DISTINCT) And Indexes
Why won't this query make full use of an index on (site_id, ip)? Along with that index, another index exists (site_id, page_type), and it arbitrarily picks that one. Code: SELECT COUNT(DISTINCT ip) as uniques, site_stats.* FROM site_stats GROUP BY site_id ORDER BY uniques DESC LIMIT 10
View Replies !
View Related
Select All Records From One Table That Does Not Appear In Another Table
I need to select all records from tableA that does not appear in tableB. I am using the following query that does work but is very very slow. Is there anything i can do to speed up the query? select tableA.ID, tableA.Name, tableA.Surname from tableA where tableA.ID != ALL (select tableB.ID FROM tableB where tableB.status = 'inserted' or tableB.status= 'edited' or tableB.status = 'deleted' ); I have as example 6000 records in tableA and 2000 records in tableB. tableB is used to track which records have been inserted, edited or deleted from another system so the query should return all records that do not exist in the other system yet. This query runs for about 3mins and just gets slower the more data there is. Can anyone suggest anything that could make this a sub second response?
View Replies !
View Related
Distinct Records
Distinct records not working, I still get duplicate's $query = "SELECT DISTINCT(ITEM), `NAME` , `PRICE` , `IMG`, `id` FROM `iflorist` WHERE `PRICE` >= 0 and `PRICE` <= 29.99 ORDER BY `NAME` DESC LIMIT 0 , 999"; If I take out the "id" field no duplicates will output, But I need "id" included. "id" is setup currently with : INT, INDEX, and auto_increment ??
View Replies !
View Related
Optimizing Slow Queries On Millions Of Records
I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I have a webpage that executes 9 queries one after the other, and then displays the results on the webpage. When the database was empty, this process was very quick. But, as the DB grew, it became slower. Now it takes about 38 seconds for all queries in the example below. MySQL 4.1 on Windows I have tried different indexes, and they do help and are used, but the queries still seem to take a long time. Code:
View Replies !
View Related
Can I Get A Total COUNT From A UNION DISTINCT?
I've got a rather large table (18M rows) of customer prospects and I need to count the number of rows that can be returned for different customers. The wrinkle here is that I have over 20 different "scores" for each prospect and they can be combined in virtually any combination which means I cannot index them all in one index as that's too many for one index and the leftmost prefix rule means the indexes would be ignored if one of the scores wasn't used. The fastest query I've been able to construct is using UNION with individual indexes for each score: SELECT COUNT(memberID) FROM members WHERE score1 > 100 AND areaCode IN (1, 2, 3, 4) UNION DISTINCT SELECT COUNT(memberID) FROM members WHERE score2 > 100 AND areaCode IN (1, 2, 3, 4) UNION DISTINCT SELECT COUNT(memberID) FROM members WHERE score7 > 100 AND areaCode IN (1, 2, 3, 4) Now unfortunately this is giving me three counts of memberIDs, one for each SELECT statement, whereas I want a total number of distinct rows for the whole statement. I can't just add the three results together as each count can contain some duplicates which means the total would be inaccurate. If I remove the COUNT() from the queries (i.e. SELECT memberID FROM members) it returns large recordsets of around 50K rows but I get a list of distinct IDs and doing a countrows on this is 100% accurate. But as you can imagine this is slow and I don't actually need this large recordset it returns. Is this slow because of the large numbers or rows or because it's merging the list to be distinct? If it's the latter then getting a distinct COUNT() will be slow anyway. But, assuming it's not the merge that is slowing things can anyone suggest a way of getting an overall count of distinct records?
View Replies !
View Related
Slow Queries, 4 Million Records, Need Educated Advice!
I have created an app a few years back to store some records in a DB. According our calculations we were never to exceed 500,000 records in the DB. Seems we were off by a decimal point or so. I set up a FreeBSD box with MySQL three years back and it has been filling up. One table has over 4,000,000 records. Yes, four million. As a web developer and not a DBA, I have struggled to upkeep the server the best I can. As of the last one million records the server has been struggling to keep up with multiple requests and as you can imagine the user base is growing too. Hardware: Dual Xeon 3.06 Ghz 4 GB ECC RAM 800GB RAID5 SATA array Software: FreeBSD 5.3 Apache 2.0 PHP5 MySQL 5.0.2 Basically I have to perform a search on one of two columns in this huge table (10 columns, 4 million rows). The table is MyISAM with a single primary key that is used largely for updating row data. Most records are ten digit numbers for one column and a ten digit varchar for the other, but sometimes either column can be a series of characters up to 100 chars long, so each column is set for varchar(100). The action performed is SELECT count(*) FROM tableName WHERE col LIKE "%123%" then... SELECT * FROM tableName WHERE col LIKE "%123%" LIMIT 0,25 with "%123%" being any random string typed into a search window.
View Replies !
View Related
Using Count(*) With Distinct To Determine Multiple Copies
I have an sql table 'messages' that stores peoples messages, in the form below: (Cname = conversation name) User Recipient Cname Subject Message Fred Bill Weather Rain Levels I think they are low Fred Harry Traffic Congestion Its getting worse Fred Bill Traffic Congestion Its getting worse Fred Harry Money Savings The interest rates dropped Fred Bill Money Savi
View Replies !
View Related
Selecting Non Duplicate Records Without Using DISTINCT
how not to display duplicate records without using DISTINCT. Say i have a database: ID Channel Description 41 Africa News Latest news from africa 42 Technology News Latest Tech News 43 PC News Latest PC related news 44 Africa News Latest news from africa I run the query 'SELECT DISTINCT * FROM Mytbl' and obviously get the results as shown above with 'Africa News' appearing twice as the primary key ID makes it unique. How can i return only one of the Africa entries in the results while still having the primary key value returned as well? Unfortunately with this particular app it is inevitable that duplicate values will occasionally be added to the database but i never want duplicates to be returned from a search query. Can this be done?
View Replies !
View Related
Distinct Records Ordered By Date
I have the basis of a forum similar in workings to this one. I am using perl with MySQL the problem i have is a simple SQL one. I have a table storing all post data including fields. forumID, threadID, postID, postTitle, datePosted, timePosted I want to list the threads in the order of last posted/replied to. So similar to this you click on the forum you want view and you get a list of the threads in that forum with the most recently active at the top. I don't mind how much data i can get for each thread, i can handle just getting the threadID and then i can loop back through it again and get the specific data for that thread (infact this would be preferable as it would give me more control although would work the server alittle harder). the closest SQL i have to working is: SELECT DISTINCT threadID FROM postdata WHERE forumID='$fid' ORDER BY dataPosted, timePosted DESC; but the order seems not to change.
View Replies !
View Related
Joining Tables For Distinct Records
I have two tables (table1, table2) that share common data. Table1 has 50 distinct entries while table2 has 10 distinct entries. I want to select all the data from table1 that IS NOT in table2. So, if the data is in table2, don't show it. So I should get 40 results.
View Replies !
View Related
Distinct List Of Parents With A Child Count Result
Parent (id, name) Child (id, parent_id, session_name) I'm trying to write a query that will have one output row per Parent. Each row will show: parent.id, parent.name, count of children for that parent AS children_count Some parents have no children. Those rows could return null or 0 for the children_count field. I know multiple methods external to SQL to solve the overall problem such as returning the join and counting where child.ids (for the same parent) is not null. ==>> but is there a way to do it in one SQL query that returns just the info I need?
View Replies !
View Related
Count Related Records, But Show Records With NO Related Records Also
cl_items ======== it_id (pk) it_ownerid it_name cl_offers ======== off_id (pk) off_itid (fk) -> to it_id off_whoid My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers) I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also. Is there any way to do this with mysql only?
View Replies !
View Related
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
View Replies !
View Related
Very Slow Select
The line indicated below from my php script is very slow (about 10 seconds). I have this field indexed so I thought that it would be much faster. Could someone tell me what might be wrong? I'm also including the dump of the table definitions. This is a cd cataloging database. Right now the filenames table is empty and I'm trying to populate it, but at the rate it's going it would take days. I have about 700,000 records in the 'files' table, but none in the 'filenames' table yet. Code:
View Replies !
View Related
Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost: SELECT *,count(*) FROM cancons c, musics m, discos d, r_discos_cancons rdc WHERE c.c_id_music = m.m_id AND rdc.rdc_id_disc = d.d_id AND d.d_id_music = m.m_id AND m.m_id = 24 GROUP BY c_id note: cancons (ca) = songs (en) discos (ca) = cd's (en) music (ca) = musician (en) don't worry for the WHERE part. i need it because of the foreign keys. this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think. I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.
View Replies !
View Related
Slow Mysql Select *,
I have a sql table with more then 20,000 rows in a table called summary and when I run "select * from summary" it returns a exucutions time of 0.5 seconds in php which is kinda slow if you ask me, now I know that the * type for select isnt the fastest way to fetch data but its the most convenient for me.
View Replies !
View Related
SELECT WHERE IN To Damn Slow!
MySQL 5.1.30 x64 Windows XP x64 w/ latest updates. -- Table Information -- InventoryKey = Primary Key ContInvKey = Indexed OwnerType + OwnerKey = Indexed I am trying to do a SELECT WHERE IN and it's taking over 500 seconds to return results! This statement: SELECT DISTINCT inventory.ContInvKey FROM inventory WHERE inventory.OwnerType = 1 AND inventory.OwnerKey = 18 AND inventory.ContInvKey > 0; ..takes 0.15 seconds to run and fetch 21 rows. However, if I do this statement: SELECT * FROM inventory WHERE InventoryKey IN ( SELECT DISTINCT inventory.ContInvKey FROM inventory WHERE inventory.OwnerType = 1 AND inventory.OwnerKey = 18 AND inventory.ContInvKey > 0 ); .. it takes 500+ seconds to run and return 108 rows. Something is definitely wrong here. I can repeat it every time. I monitored the server ( I am the only connection ) and I see the server spike instantly when the query is received and then it flat lines. There doesn't appear to be any activity for 499.85 seconds. It's idle!
View Replies !
View Related
|
TRACKER
