Measure/Analyze Query
I am sampling on this query:
SELECT * FROM tableA A INNER JOIN tableB B
on A.docnum like concat('%',partnum,'%')
I need to find out exactly how fast this one line of query is and try to improve the speed little by little. Well, even a few seconds of speed would help since I have about 20 columns.Is there a way in MySQL to check the duration of a query?Also, I was wondering if there is a function to return some stats on each section of a query (dissecting) and how long it takes to run each section of that query.I want to know where it lags and where/what it does to I can better understand what happens when it is running.
View Complete Forum Thread with Replies
Related Forum Messages:
Measure Execution Time
I would like to measure the execution time of my queries in order to evalutate the performance in/decrease for different changes. Even if I use 'sql_no_cache' the query executes MUCH faster the second time I run it, and is therefore not representative of the true performance. How to I avoid this problem? I'm using MySQL 4.1 on a windows server 2000. I be glad to get an explanation to why the query executes faster the 2nd time.
View Replies !
Measure Execution Time Of Mysql Script
I am trying to measure the total execution time of a mysql script that I run using the mysql command line client in batch mode (-e "source myscript"). The --tee option does not work for the batch mode so that I could then gather and add up the execution times of the SQL queries as logged by the server. Adding a tee command in the script itself is not working. Using the time command (in Linux , and timex is not available) on the mysql program is also not helpful as I think it does not include the execution time of the queries on the mysql server side, but only the system and user times corresponding to the client process.
View Replies !
Measure Size Of Multiple Insert In Bytes
How can I measure the size of a mutiple insert statement in bytes? Ex: insert into table1 (field1,field2) values ('a',111),('b',222),('c',333); Is there a tool or a sql-command that could give me the size in bytes of the queries I'm sending to the server?
View Replies !
ANALYZE...
Is it neccessary / advantageous to run "ANALYZE table" immediately after every UPDATE / INSERT ? Or would that be too much overhead?
View Replies !
HOW TO Analyze Tables And How Often To Do So?
I am an ex-Oracle dba - I am trying to apply simular practice on Mysql DB. In Oracle you need to re-analyze the tables if there is a lot of inserts / deletes / updates going on and I assume it's the same on a Mysql db? True or False? I am using a innodb storage eninge if it matters - what practice do you guys out there apply on your databases? Do you ever analyze the tables or not? and what rule or guide lines do you use? ie how would you tell if it's time to do it or if your index data is OK?
View Replies !
Analyze Table
I'm having a tough time understanding what ANALYZE TABLE does. Mysql.com says "ANALYZE TABLE analyzes and stores the key distribution for a table" Key distribution being index or indexes? Why does it store it, isn't it stored already when index is created, or when row is inserted (if index already exists). Already stored and waiting to be invoked by the Opimizer?
View Replies !
Analyze Index Usage
I am running a SELECT query that is taking approx. 200+ seconds to complete. I believe I have the tables indexed properly, but apprarently not. How can I determine what indexes a query is using and evaluate the execution plan (as you would in MSSQL)?
View Replies !
Analyze Warnings From Mysqlimport
Is there a possibility to analyze the warnings that I receive when I'm importing data to a mysql database by mysqlimport.I can find the complete data in the database but the message I receive from the mysqlimport allways tells me ...Records: 157 Deleted: 0 Skipped: 0 Warnings: 157 I think that there is just one column that produces these warnings but I don't know which column is responsible for this.
View Replies !
ANALYZE, OPTIMIZE, CHECK, REPAIR
when to use the ANALYZE, OPTIMIZE, CHECK, REPAIR commands. From what I can tell, ANALYZE and OPTIMIZE are for making the table optimal to increase performance. CHECK and REPAIR are used to find & fix corruptions. ANAYLZE takes a lot less time to run than CHECK. The questions I have are: 1. What is the general rule of thumb on regular (daily) maintenance of a MySQL DB? 2. Will ANALYZE report table corruptions? If so, I don't even need to call CHECK, I can directly go do REPAIR? 3. If the anwer to #2 is no, meaning I have to run CHECK TABLE, should I separate the optimization exercises from the find-corruption-and-repair routines?
View Replies !
Is It Safe/advisable To Run Analyze On All Your Tables?
I have read up a bit that running analyze on your tables is a good idea. So i was thinking of setting up a cron job to run analyze on all the tables in my db either daily, weekly or monthly. Just want to check first, is it safe to do this on a live database? Same goes for optimize (although i have heard for optimize you need to have no-one hitting the db?). So..is this a good idea? and how often should i run this script?
View Replies !
Analyze Table - Optimizing Time-consuming Queries
I'm starting a website-counter service, so basically my site requires a lot of mysql activity. Here are the two tables I use: CREATE TABLE `traffictrack` ( `id` int(11) NOT NULL auto_increment, `nr` int(11) NOT NULL default Ɔ', `ip` int(10) unsigned NOT NULL default Ɔ', `ndate` datetime NOT NULL default ��-00-00 00:00:00', PRIMARY KEY (`id`), KEY `ndate` (`ndate`) ) CREATE TABLE `tracker` ( `nr` int(11) NOT NULL auto_increment, `email` varchar(70) NOT NULL default '', `password` varchar(220) NOT NULL default '', `mdate` datetime NOT NULL default ��-00-00 00:00:00', `ip` int(10) unsigned NOT NULL default Ɔ', `image` varchar(200) NOT NULL default '', `friendid` int(11) NOT NULL default Ɔ', KEY `mdate` (`mdate`), KEY `nr` (`nr`) ) I have some queries that take up more than 1 second: PHP SELECT INET_NTOA(traffictrack.ip) as ip, tracker.image, UNIX_TIMESTAMP(traffictrack.ndate) as ndate, tracker.friendid, ( UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(traffictrack.ndate) ) as dif FROM traffictrack LEFT JOIN tracker USING(ip) WHERE traffictrack.nr = ?' AND DATE_FORMAT(traffictrack.ndate, '%Y-%m-%d')=��-07-23' ORDER BY traffictrack.ndate DESC LIMIT 20 also: PHP SELECT COUNT( traffictrack.ndate ) AS total, tracker.image, tracker.friendid, UNIX_TIMESTAMP(traffictrack.ndate) as timest FROM traffictrack LEFT JOIN tracker USING ( ip ) WHERE traffictrack.nr = ?' AND ( UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( traffictrack.ndate ) ) < 600 GROUP BY tracker.nr LIMIT 0 , 30 I believe it's because my tables are badly indexed, I added an EXPLAIN before these two queries, they both ended up with: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE traffictrack ALL NULL NULL NULL NULL 34246 Using where; Using temporary; Using filesort 1 SIMPLE tracker ALL NULL NULL NULL NULL 20972 If someone knows how to speed up my queries, please help.
View Replies !
MySQL Administrator: Analyze Backup File Content Failed
I installed MySQL Administrator tried to restore from a sql dump file. No luck. When I run "Analyze Backup File Content" it says "The analyzation failed." I inherited this dump file and don't have much detail about it and can't really go back to the contractor who supplied it. Any pointers on how to get this table and data into my version of MySQL?
View Replies !
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
View Replies !
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
View Replies !
Slow Query->Efficient Query
I have a query that is running really slow !!!! I have joined on Key fields and indexed the tables fully but it is still solw. -------------------------------------------- select d.id, a.signed, u.Forename, u.Surname, d.paid, p.date, d.payment, p.amount, d.acctual from details d join poten a on a.id = d.id left join recieved p on d.id = p.id left join users u on a.signed = u.userid where d.paid > '01-Dec-2005' and d.authorrceived is not null and d.authorrefused is null and ((d.payment starting 'E' or d.payment starting 'e') or (d.payment starting 'Q' or d.payment starting 'q' and p.target = '500')) order by d.paid, a.signed, d.id
View Replies !
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
View Replies !
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
View Replies !
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
View Replies !
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
View Replies !
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
View Replies !
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
View Replies !
Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views. I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them? If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?
View Replies !
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
View Replies !
Sub Query And Count Query
i have a database with the following structure id | MoveDate | ItemId | SiteID (a new entry is entered when an item is moved from 1 site to another) and i am trying to forumlate a query so that i can count how many days each item was at a specific location so lets assumes i have the following data 1 | 01/01/2007| 1 | 1 2 | 03/01/2007| 1 | 2 how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5
View Replies !
Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query? I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result? If so, what's the PHP syntax for this?
View Replies !
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
View Replies !
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Can I Use An Either/or Query?
I am using the following query to generate a web page. Searchterm is derived from a search form. $query="select * from jobs, items where jobs.JobNumber like '$Searchterm' and items.JobNumber like '$Searchterm'"; The data from the jobs table goes in a general form describing a specific job. The data from the items table goes in a separate form which lists all of the items associated with that job. This works fine if there is data for the selected job number in both tables but in some cases, the data only exists in the jobs table - there is no corresponding data in the items table. What I would like in that case is to output the jobs table data and simply leave the items output blank, but since there are no records meeting the criteria of the query, nothing is selected. Is there a way to create a query so that it will do what I want. If not, can it be done with an either/or statement?
View Replies !
Query Within Query
I have a query that I've gotten to work fine in SQL Server, but when I tried to run the same query on an indentially structured database in MySQL, I get a syntax error. This is the query: select user_id, score, (select count(*) + 1 from ericTest where score > A.score) as rank from ericTest A Essentially, it's a table of user_id, score, and the user's rank, which is calculated by the inner query. Thoughts on what's wrong?
View Replies !
1 Big Query
I have a question about MySQL in general. I'm working with a PHP page that has a couple of nested foreach loops that access the DB. It works out to be a little over 8,500 small queries. This page takes minutes to display. However, when I select basically the same data all at once and then sort through it using PHP arrays and such it takes a couple of seconds.
View Replies !
AVG Query
I have a sql problem I need your help with. I have two tables, one called the user_table having columns user_id and user_rating and another called user_ratings having columns seller_id and rating. The two tables are joined as user_id=seller_id. I wish that every time a new entry is made in the user_ratings table, the user_rating column in user_table be updated with the average rating for that user.
View Replies !
Fix My Query
All right I've sat here for a few hours... still can't get this to work: Code: SET @spot1 = LOCATE('.net/',wp_posts.post_content); SET @spot2 = LOCATE('target="_blank">Download',wp_posts.post_content); SET @spot3 = @spot2 - @spot1; IF @spot1 > 0 THEN UPDATE wp_posts SET post_content = REPLACE(post_content,@spot1,@spot3); It has a problem saying wp_post table does not exists but it does.also it has a problem with my if statement.
View Replies !
For A Query
I have a table feedtagrelation with two fields. FeedID and TagID. FeedID TagID 1 1 2 1 1 2 3 2 4 5 2 2 5 4 i want a query such that it gives all the feeds that are present in more than one tagid (can go up to n level) example :- a query to find feed ids such that they have tagid both 1 and 2 .
View Replies !
IN For A Query
Stuck on a query which i think uses a IN clause but I'm not sure. I have queried a table for a list of reference ids for objects and a list of ids of users who created these objects. With both of these i then created an array $tempreferenceid (for the reference ids for objects) and $tempuserid (for the ids of users). I now need one select query for another two tables 'object' and 'adminsession' for the details of each object using the object id and the details of each user using the user id. this is what I have... SELECT object.url, object.file_url, object.icon_url, object.title, object.text, object.description, object.keywords, object.type, adminsession.useralias FROM object, adminsession WHERE object.id IN '$tempreferenceid' AND adminsession.userid IN '$tempuserid'";
View Replies !
Php Query
I only have php experience with mailforms but those a are no brainers!. so please help a brother out see, I have a mysql table like this one. as you can see, there are a lot of columns with zero`s values on that table so lets pretend someone access php login page, and imputs their id,name and then press login. Would it be posible that the page then returns only all the non-zero values for their specific record?
View Replies !
LIKE Query
ID----Bedrooms----Neighborhoods 1.......1................1,2 2.......1,2..............3 3.......3.................2 Let's say a user wants an apt. with 1 OR 2 bedrooms AND must be in neighborhood 3. (i.e. only record 2 should show up). I tried: SELECT id from TABLE where bedrooms LIKE '%1%' or bedrooms like '%2%' AND Neighborhoods='3' but it's returning records 2 AND 1 instead of just 2. It's like it doesn't even consider the 'AND'.
View Replies !
Looking For Help With A Query
I have a table that holds the high scores for users on a games website. The table holds the gameID, the userID and their score. I want to fetch all the highscores for a certain game and rank those scores and then find out what rank a certain user ID is. But all I want returned is a single row which shows the userID, the gameID and the rank. I have accomplished showing all of the high scores for a game but this returns over 900 rows and I don't want to have to put all of those rows into a PHP array just to fetch out 1 row, seems a waste of resources. It has been suggested i use within-group aggregates but I can't seem to get the query to work. I either get errors or the query locks up the server. To give you an idea on what I have so far: Code: SELECT MAX( t1.score ) AS highscore, t1.mid FROM games_scores AS t1 WHERE t1.gid =10 GROUP BY t1.score ORDER BY t1.score DESC That query will show all the highscores and the userid that had that high score and rank them. But the problem with that is that it returns 900 odd rows and I only want to return 1 row which shows what rank the user is.
View Replies !
NOT IN Query
I've developed with MS SQL for a while, but not with MySQL... I'm running into an error when I try to run the following query: Code: Select * from seasons WHERE Seasons.SeasonID NOT IN (Select SeasonMembers.SeasonID from SeasonMembers where SeasonMembers.AthleteID=2) Both queries function when I run them seperately... is there any reason this shouldn't work in MySQL? There are three tables, athletes, seasons, and SeasonMembership, which holds the Athlete key and Season key... The query is for a drop-down menu which allow you to add an athlete to a season that it is not already a memeber of.
View Replies !
'LIKE' Query
I need a little advice on a mysql query that involves to tables. I want to link the 2 tables with the LIKE operator something like this: SELECT table_A.var_1, table_B.var_2 FROM table_A, table_B WHERE table_A.var_1 LIKE '%,table_B.var_2,%' table_B.var_2 contains values like: ,123, ,1223, ,5201, .... Of course the above query will not work because table_B.var_2 is treated as text and not a variable. Any ideas how to get around this?
View Replies !
Query Help
I have two tables and i want to perform a join Table A with column w Table B with column x, y, z ( y and z are distinct, i.e if y has a value than z is null or if z has a value than y is null for a given x) i want to join table A to B with A.w = B.x but I want to combine y and z into one single column in this join operation, does anyone know how ?
View Replies !
Query IN
The below query fails. It shows Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource What have I done wrong? $buyerID="1,2,"; /*EXPLODE BUYER ARRAY*/ $buyerID = substr($buyerID, 0, -1); $buyerID= explode(",",$buyerID); $query = "SELECT * WHERE equipment.buyer IN ($buyerID) GROUP BY equipment.itemid ORDER BY equipment.itemid ASC"; $result = mysql_query($query); $line=mysql_fetch_array($result, MYSQL_ASSOC);
View Replies !
|
TRACKER
