Slow Join On Large Tables
I have two tables:
D (500,000 recs), and DL (2,500,000 recs)
D has a PK and an index on HLQ. DL has a PK and an index on ID.
The following SQL:
SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ
produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort
DLeq_refIDID4D.DLID1Using index
The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).
key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M
It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?
View Complete Forum Thread with Replies
Related Forum Messages:
Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else. I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger. This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen. Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go. I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.
View Replies !
Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl= e categories of entries, the database is quite large about 200 meg.=20 I would like to know if using join tables is more efficient than storing th= e keys in a varchar field then within the second loop doing a where in (1,2= ,3,4) where the 1,2,3,4 are the stored category keys in the varchar field = rather than a where in (1), where 1 is the pirmaryID of the entry for insta= nce ?
View Replies !
Large Table, Slow Query Question
I have a table with ~800,000 records. I need to grab random rows from the table based on certain criteria. The problem is that average lowest subset to grab the random row is around 200k. Here is what I'm trying to do: There are 4 columns: data,n1,n2, and n3. I need to get the value of the data column based on criteria using the n1-n3 columns. The most common query is SELECT data FROM table WHERE n1 = ? The problem is that n1 can be only 1 of 5 possiblities. When the table is finished being populated there will be roughly 1.5 million records and 250k for each value of n1. Of course, I have an index on each n column. Right now with just the 800k records it can take over a second, sometimes multiple seconds to run the following in order to get a random row from that subset: SELECT COUNT(1) AS total FROM table WHERE n1 = 3; index = random number from 1 to total SELECT data FROM table WHERE n1 = 3 LIMIT index,1; How can I speed this up? I need it to take less than half a second if possible. Thank you.
View Replies !
UPDATE Large Number Of Rows With CASE Is Very Slow
UPDATE table SET rating = (CASE uniq WHEN 'ACD' THEN 0.6857 WHEN 'GGYB' THEN 0.5831 WHEN 'QUCG' THEN 0.5831 WHEN 'CYP' THEN 0.5831 ... ELSE 0 END), score = (SELECT @t := 1/(1+rating))*votes_up - (1-@t)*votes_down; The table has the column `uniq` as PRIMARY KEY of type VARCHAR(10), and about 500 rows. I need to update the entire table with this query. The problem is the query runs extremely slowly, taking 4.6529 seconds to run. I realize the index is not the most efficient, but is it the main culprit?
View Replies !
Join Too Slow
I'm creating a query that use Join clause. I tested it in MySQL 4.0.24 and with MS-ACCESS. . . . in MySQL is slow!!! any suggestion ?
View Replies !
Slow Join On Otherwise Fast Views?
select * from vw_fielddata takes 16ms to return just four rows, why does select * from vw_fielddata firstname join vw_fielddata secondname on secondname.AccountID = firstname.AccountID take over an hour? The views themselves contain joins on tables and further views, and although AccountID isn't a primary key from the tables it's gathered from it is a foreign key in them (I assume this makes it indexed?).
View Replies !
Slow Query W/ Join & Ordering
I am trying to figure out why I have a hugely slow query (~2 seconds in my testing environment). Details are below: It involves two tables, products and vendors. Products is a huge table, so I will only include the (ostensibly!) relevant fields in its description: CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `vendor_id` smallint(6) NOT NULL default Ɔ', `product_code` varchar(255) NOT NULL default '', `internal_name` varchar(255) NOT NULL default '', `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`product_code`), KEY `vendor_id` (`vendor_id`) ) ENGINE=MyISAM; Vendors are much more straightforward: CREATE TABLE `vendors` ( `id` smallint(6) NOT NULL auto_increment, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM; The following query executes in no MORE than 0.01 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name FROM products as p ORDER BY p.product_code ASC LIMIT 0, 30; And has the following attributes: +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ | 1 | SIMPLE | p | index | NULL | product_code | 257 | NULL | 25124 | Using temporary | +----+-------------+-------+-------+---------------+--------------+---------+------+-------+-----------------+ When I join with the vendors table, so that I can fetch the vendor's name for each product, I use the following query, which takes about 1.88 seconds: SELECT DISTINCT p.id , p.product_code , unix_timestamp(p.lastmodified) as lastmodified , p.internal_name , v.name as vendor_name FROM products as p LEFT JOIN vendors as v ON v.id=p.vendor_id ORDER BY p.product_code ASC LIMIT 0, 30; It has the following characteristics: +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary; Using filesort | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+---------------------------------+ Note the addition of the filesort. I'm unhappy enough about the temporary, which I don't really understand, but the filesort is, I'm fairly sure, killing me. Closer investigation (or maybe just common sense if you aren't a MySQL newbie like me) shows that the ORDER BY clause is responsible, for when I join without the ORDER BY, my query time goes back down to 0.01 seconds or so: mysql> explain SELECT DISTINCT p.id -> , p.product_code -> , unix_timestamp(p.lastmodified) as lastmodified -> , p.internal_name -> , v.name as vendor_name -> FROM products as p -> LEFT JOIN vendors as v ON v.id=p.vendor_id -> LIMIT 0,30; +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ | 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 25124 | Using temporary | | 1 | SIMPLE | v | eq_ref | PRIMARY | PRIMARY | 2 | te_inventory.p.vendor_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+--------------------------+-------+-----------------+ Any clues on how I can get the execution time to go down when I am sorting? I'm also curious why MySQL is using a temporary table,
View Replies !
Large Join Query
I am at a loss. Right now I have a PHP script that needs to run a huge join query. They query joins two tables of approximately 200,000 records. I'm running mysql 3.x so I can't use a view... at this point indexes don't seem to help. I am thinking the only way to do this is with script processing
View Replies !
Slow Execution For A Left Outer Join Query
Whats likely to be the cause of slow execution for a left outer join query? The original query joins three tables but even if I narrow it down to one it still takes a long time to execute. $query = "select distinct materials.* from materials"; $query .= " left outer join materials_products on materials.material_id = materials_products.material_id"; There's 914 rows in the materials table and 1348 row in the materials_products table Is it likely to take a long time for this amount of data or is there likely to be a problem in the table(s) set up or query?
View Replies !
Equi-join On A Large Table?
I'm running on MySQL 5.x Let's say I have the following table of data: Code: Table: test USER_ID|CLASS_ID 12 13 14 22 24 32 34 I'm trying get all the classes that these users have in common. E.g. If i ran a query to do this I would get class_id 2 and 4, because those are the only classes that all three users have in common. I think I want to do a equi-join, something like: Code: ....
View Replies !
Optimizing Join W/ Large Table
Here is a high level description of my tables I have a table called Items items are grouped by an ID, but it is NOT unique I have an index on ID for the Items table and a table called CompletedItems completed items consolidates information about the items, and here the ID is unique. I have unique index on ID for the CompletedItems table What i need is a query that will return all item id's which have not been completed. I have this query: select distinct(items.id) from items left join (completeditems) on (items.id = completeditems.id) WHERE completeditems.state is null; this gives me the data I want except there are two problems a) this query takes a long time to run. while it is running, the completed items table is locked, which causes several other processes to block while this is happening. to give you an idea of scale, the items table has about 20 million records in it, and the completed items has about 4 million.
View Replies !
Help Me Refine This Large Join Query
$sql = "SELECT f.*, p.post_time, p.post_username, u.post_subject,u.post_text,v.username FROM (( " . phpbb_forums . " f LEFT JOIN " . phpbb_posts . " p ON p.post_id = f.forum_last_post_id ) LEFT JOIN " . phpbb_posts_text . " u, phpbb_users v ON u.post_id = p.post_id and ON v.user_id=p.poster_id) ORDER BY f.cat_id, f.forum_order"; Right now its select all the posts from every forum, I just want it to select the last post from every forum and the second join on phpbb_users don't seem to be working as the user_id doesn't seem to be matching on the poster_id, any suggestions? The query also doesn't work, I want phpbb_posts to left join to two tables, posts_text and posts_topics, how to I do that?
View Replies !
How Do I Rewrite A Slow Subquery Into A Fast Join? (Prolly Real Easy For A Non-noob To Answer)
I used subquerys because they made more sense to me, until the table got "a lot" of data in it (not really... just 1000 entries) - then all querys including subquerys slowed down to 4-5 secs EACH!! :) This is insanely slow. What I am doing here below is looking into what messages a user has already read in the subquery, so that none of the ones he already has read will EVER again show up for him. So, how do I rewrite this subquery: NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) From this entire SELECT: SELECT halfref, brokensms, DATE_FORMAT(arrived, '%y'), DATE_FORMAT(arrived, '%m'), DATE_FORMAT(arrived, '%d'), DATE_FORMAT(arrived, '%H'), DATE_FORMAT(arrived, '%i'), priv FROM $halft WHERE sender = $nr and halfref NOT IN (SELECT reffen FROM $readt WHERE sender = $nr) order by id desc limit 1 Into a faster join of some sort? I'd aprichiate if you told me what each part of the rewrite actually does, because I been reading about joins for a day now and still don't get them at all!
View Replies !
Slow Joining Of Two Tables
I am having trouble combing data from two tables. The tables have exactly the same layout, but have different : mysql> describe MONITORINGUNIT1_DATA; +-------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------------------+-------+ | dt | datetime | | | 0000-00-00 00:00:00 | | | wg | float(20,3) | YES | | NULL | | | dflag_wg | tinyint(4) | YES | | NULL | | +-------------+-------------+------+-----+---------------------+-------+ mysql> select count(dt) from MONITORINGUNIT1_DATA; +-----------+ | count(dt) | +-----------+ | 24144 | +-----------+ 1 row in set (0.00 sec) mysql> select count(dt) from MONITORINGUNIT2_DATA; +-----------+ | count(dt) | +-----------+ | 1464 | +-----------+ 1 row in set (0.00 sec) Very briefly, [dt] contains an hourly date/time stamp representing when the reading [wg] was taken. [dflag_wg] contains a integer that describes the data (over threshold, under threshold, etc). The DB is populated automatically by a Python script that executes once per hour. If I want to get the overlapping data (with the same date/time stamp) I use this query: Code:
View Replies !
Slow Insert To Tables
I am inserting 70,000 Records to one mysql table, this table is a simple table with no indexes or primary keys (it consists of 3 columns), add those records takes me like 35 mins on a P4 Machine with 2.5GB of RAM. It is very slow comparing to Access or SQL Server.
View Replies !
Federated Tables Slow? (like 4.5 Hrs For A Query)
I've got a problem with federated tables. I'm using MySQL 5.1 (with InnoDB as the default table type) on a Win2K server, on which I've got four federated tables pointing at four MyISAM tables on a MySQL 4.1.11 server. Of the four tables, three of them run just fine, and I can retrieve data quickly with no problems. The fourth is a sheer pig. While they have different columns, all four tables are roughly as complex as each other, all having the same features and developed by the same team. The most obvious difference, and what I suspect might be the problem, is that the first three tables have between 150 and 1,000 records, the fourth table has closer to 15,000. Still, there isn't that much lag when I'm pulling from the smaller tables, and the lag is really serious when I'm pulling from the larger one; I ran three queries last night to test, and I could pull data from the smaller tables in about 5 minutes, but the larger table took 4.5 hours- possibly because it was joined with two other tables, but the joins on the smaller tables didn't cause this kind of problem. The second obvious difference is the fact that I'm pulling from a MyISAM table into a federated table... from which I would like to store into an InnoDB table, but it ends up timing out quite a bit. Connecting to the database I've federated to isn't a problem. It responds to a PHP frontend lightning-fast. It's just my federated tables that suck so bad. I wouldn't even use federated tables, but I need to pull from the MyISAM database for storing historical records of inventory. What am I doing wrong, and what can I do to speed things up?
View Replies !
Large Tables
So i've got a table with 15 million rows. id is the primary key SELECT COUNT(id) FROM listings WHERE id != 0; Takes about a minute to load... which is insanely slow.. anyone know what could be causing this?
View Replies !
Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example: select myfunc(x,y,z,a,b,c) from events where energy>3; But as far as I know there is no way of defining your own functions in SQL
View Replies !
Connecting Three Tables With Left Join And Ordinary Join
I have 3 Mysql tables: Week (with columns day and hour) Activity (with columns day, hour, activityid and ac_text) Person (with columns name and activityid) I would like to create a scheme showing the activities during a week sorted on days and hours. If I ignore the person table I can fix it with the statement: Select …. From week left join activity on (week.day = activity.day) and (week.hour = activity.hour) order by day, hour I can then make a loop (I am usin asp.net) that writes the activities. My problem is when I try to combine the persons to the activtities in an given hour. How do I do that ? (activity.activityid = person.activityid). I have a little extra question. When I make the join above and print the result (day, time and activity) there isn’t any output if no activity matches a given day and hour. How do I do when I always want to print day and hour and add activity where such exist.
View Replies !
Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life: If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient? The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key. The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?
View Replies !
Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user. Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details. Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?
View Replies !
Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables. My question for performance, as this table grows should it eventually make a new table? If so here is my current select code select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1' Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?
View Replies !
Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....
View Replies !
Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records. This is very new to me and I am noticing that my queries are really slowwwwww! What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.
View Replies !
One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I use only one table with an indexed column for the ID and another column for the data, or should I use 500 tables each with only one column for the data?
View Replies !
Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size. The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table. But the question I have now, can unused large tables affect the perfomance of Mysql? I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )
View Replies !
One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this. My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group. If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?
View Replies !
Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems? as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?
View Replies !
Slow Performance :: Navicat To Import Tables From An ODBC Connection
have been using Navicat to import tables from an ODBC connection I have. The import goes extremely fast but once the data is locally stored and I try to manipulate it by joining tables, the program goes slower and sometimes even crashes it. If the same data is however imported into a table in MS Access, and a query is created it runs fine. I would like to know if there is a way to improve the performance of my MySQL database as the reason I was using it is because of its scalability. I was wondering if there was something I was doing wrong or could do better. The Server the data is downloaded to right now is has the following specs: Windows XP Pro Intel Core 2 CPU 2.13 Ghz 2 GB RAM
View Replies !
Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much out of the box setup, and am starting to suffer heavily with my larger tables. I have a table with 5,000,000+ rows that I have to search and do joins on. Although I have an index set up for it, the joined select will still take some 400+ seconds to return, which is obviously unacceptable. This is due to enormous HD access. Perhaps someone can help me with indexes here; I was under the impression that the index for the tables are cached in memory, and therefore permitted "instant" searchability, without having to retrieve data from each of the rows of the DB. Is there a startup parameter, or something in the mysql.ini file, that must be set to allow for this? I have the index configured properly, and have made sure that the query uses there parameters in the where clause in the same order that they appear in the index. Code:
View Replies !
Speed Of InnoDB DELETEs On Large Tables
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the following select takes a couple of seconds): [color=blue] > SELECT count(*)[/color] -> FROM UserSnap -> WHERE LogDate<now() - INTERVAL 750 DAY; +----------+ | count(*) | +----------+ | 308969 | +----------+ [color=blue] > DELETE FROM UserSnap WHERE LogDate<now() - INTERVAL 750 DAY;[/color] That delete query takes hours to run. The structure of the table is: [color=blue] > desc UserSnap;[/color] +----------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------------------+-------+ | LogDate | datetime | | PRI | 0000-00-00 00:00:00 | | | Period | tinyint(4) | | PRI | 0 | | | UserName | varchar(50) | | PRI | | | | RateType | varchar(50) | | PRI | default | | | Rate | float | YES | | NULL | | +----------+-------------+------+-----+---------------------+-------+ Any suggestions on why this is slow, and what to do about it?
View Replies !
Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL. I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools. The performance decreased gradually and crashed at ~60% completion. Now I'm planning to use xml2sql tools to convert the dumps to txt format. Then I'm planning to load this using mysqlimport. I would like to run this load faster, and on Oracle, I would use the IMP parameters such as INDEXES=n and so on. What are the recommended steps when using mysql? Should I target any of the system variables? I have a 4G ram on the server and can use all of it for the load. Any pointers?
View Replies !
Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions: 1-what can I do to avoid the system stop or time out message? 2- To use several tabels infomation should I use 'View' command or can I use other methods? 3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table? (I use postgresql).
View Replies !
Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down. I have 4 important tables here (I'm only posting the columns which I use): `auth` UID mediumint(8) unsigned PRI NULL auto_increment username varchar(32) MUL clan_id mediumint(8) unsigned MUL 0 clan_abbrev varchar(7) MUL `clans` ID mediumint(8) unsigned PRI NULL auto_increment Abbrev varchar(7) MUL Name varchar(64) icon_id smallint(5) unsigned 0 `stats` UID int(10) unsigned PRI 0 kills mediumint(9) NULL `usernames` UID int(10) unsigned 0 ID int(10) unsigned PRI NULL auto_increment Name varchar(32) Uses mediumint(9) 0 `auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`. I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`. The result should be ordered by `Uses` (from `usernames`) -- highest to lowest. The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type). Here's what I managed to hammer out with my limited knowledge of SQL: ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `clans` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND clans.ID = auth.clan_id AND stats.UID = auth.UID ) UNION ( SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills` FROM `usernames` , `auth` , `stats` WHERE usernames.Name LIKE '%storm%' AND auth.UID = usernames.UID AND auth.clan_id =0 AND stats.UID = auth.UID ) ORDER BY `Uses` DESC It works great, but the average query takes around 6s (these tables have several thousand entries in each).
View Replies !
How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi, I have two tables: table A Id. | id_result | value 1 | 1| 10 2 | 1| 11 3 | 2| 7 4 | 2| 13 table B Id. | id_result | value 1 | 1| 4 2 | 1| 1 3 | 2| 5 4 | 2| 6 How can I get sum of unique keys from table A and B (id_result) like this?: id_result | sum_table_A | sum_table_B 1 | 21| 5 2 | 20| 11 I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?
View Replies !
Join Two Tables
I have a database that has two tables and I'd like to be able to join more than one column in one table to one column in the other table....
View Replies !
How To Join 2 Tables
Anyways Im a newbie using MYSql V5 I have 2 tables (called teams & results) In teams I have 2 columns teamsindex..........teamname (Primary Key) ........1......................arsenal ........2......................man u ........3......................liverpool ........4......................chelsea ........5......................tottenham ........6......................accrington stanley (who are they :) ) In results I have 3 columns (amongst many) which contain primary key & fixture info (intiger) e.g. Recordnum..........hometeam.......awayteam (primary key) .........1......................1.......................2 .........2......................3.......................4 .........3......................5.......................6 I want to output the fixtures so instead of it displaying 1vs2, 3vs4, 4vs5 I want it to display arsenal vs man u, liverpool vs chelsea, tottenham vs accrington stanley etc I know there is a really really simple answer to this but Im really really thick, I expect its based on the JOIN syntax but can someone show me the code that would produce the results I require in order for me to get my head around it, me is very odd, I can work things out going backwards.
View Replies !
How To Join Tables?
I would like to select info from just two tables. I've tried two seperate select calls but it gives me some of the same info from each table twice. Can someone please post a "join for dummies" example on how I would do this. I've tried using "Union" but I'm still just a newbie and was even more confused.
View Replies !
Join Three Tables
I have a problem about joining three tables A, B, C: table A has the serial_number as its primary key, table B has the serial_number as its primary key but serial_number is not unique in this table (combined with category, group etc. other columns as primary key together ) table C has some common columns with table B Now I want to select the serial_number from table B with certain conditions from Table C, so I could use the following query: select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions; And then, I would like to add the serial_number in table_A but not in table B to the serial_number I just selected, so I would use something like: select distinct tB.serial_number from table_B as tB join table_C as tC Where Conditions UNION select serial_number from table_A not in table_B; is it right? I think it's the right way to do that. Another question, with the same task, can I do the query like this: select tA.serial_number from table_A as tA LEFT JOIN table_B as tB ON tA.serial_number = tB.serial_number JOIN tableC as tC WHERE Conditions.. That's what I understand about "LEFT JOIN", SQL will select serial_number from table_B and table_C with the conditions satisfied first and then join table_A with serial_number only in table_A but not in table_B. Am I right?
View Replies !
Trying To Join 3 Tables
I ma trying to create a join on 3 tables. However I can't get it to work. If I do this it works SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID; However once i try this i just keep getting an empty set result SELECT * FROM Cottage, Cottage_Photos, Location WHERE Cottage.bedrooms = '3’ AND Cottage.Cottage_ID = Cottage_Photos.Cottage_ID AND Cottage.Location_ID = Location.Location_ID; Is it possible to join 3 tables?
View Replies !
Join Tables With Ip
i want to join two tables using ip address through sql select * from db1.table1 union select * from db2.table2; this is used for join two tables from the same pc. what command is used for joining the tables if they are not in same pc ?
View Replies !
Join Four Tables
SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id added requester to sql statement SELECT appointment.appt_id, patient.pt_fname, patient.pt_lname, requester.req_fname, requester.req_lname, patient.pt_phonenum, appointment.appt_date, appointment.appt_time, language.language FROM appointment, language, patient where patient.lang_id = language.lang_id and patient.pt_id = appointment.pt_id and requester.req_id = patient.req_id
View Replies !
Join 3 Tables
i want to join three tables and i got it to do that but when want to see the results if there is not a record in all three tables the data for that person wont show up only the person who has a record in all three tables will. SELECT * FROM users,user_info,user_contact where users.id = user_info.user_info_users_id and user_info.user_info_id = user_contact_id how can i get it to show all the data and just show blank space if there is no record in one table. like the user_contact table.
View Replies !
How Do I Join These Two Tables?
I want to join two different queries into the same table. I have had a look at UNION and JOIN but they seem to be way over my head. Off peak time is Midnight to 7AM and Peak time is 7AM to Midnight. I can put the data into two separate tables (see below) but I don't know how to combine them. Code:
View Replies !
How To Join Two Tables
I have two tables, dtbl and dltbl I want to retrieve dtbl.name when dtbl.id is the same as dltbl.id This is giving me a syntax error: SELECT t1.id, t2.name FROM dltbl AS t1 WHERE did = $id AND aid = {$a->id} INNER JOIN dtbl AS t2 ON t1.id = t2.id
View Replies !
|
TRACKER
