I've got an array that contains combinations of unique MySql IDs: For example: [ [1,10,11], [2,10], [3,10,12], [3,12,13,20], [4,12] ] In total there are a couple hundred different combinations of IDs. Some of these combinations are "valid" and some are not. For example, [1,10,11] may be a valid combination, whereas [3,10,12] m...
Hi! As far as I know, mysql doesn't use index if using IN(1, 2...n) queries. Am I wrong? Or is there anything I could do to make mysql use it? I don't mean IN() subquery optimization, because this is clearly explained in the manual. Example (assuming there is an index on all fields, named index_abc): WHERE a = 1 AND b = 2 AND c = 3 - ...
I have this code wich gets WP posts, but it gets all the posts and orders them from the first to the last. I want only the last 10 post ordered from last to the earlier. What I have to change? Thanks a lot in advance! <?php $dbhost = 'localhost'; $dbuser = 'root'; $dbpass = ''; $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('...
does anybody know how to do this syntax below in mysql? without Stored Procedure and in single query only SELECT CASE WHEN COUNT(v.value) = 0 THEN ( INSERT INTO tbl_v (fid, uid, VALUE) SELECT fid, 1 AS uid, 'xxxxxx' AS VALUE FROM tbl_f WHERE category = 'categoryname' AND NAME = 'somevalue' )WHEN v.value <> 'test' THEN ( 'u...
Greetings friends, In my MySQL database, I have 'MAB' table which contains information about Gene Ids (GI_ID),and some other gene related information. 'MAB_CAN' table can contains the Gene Ids (GI_ID) only relevant to Cancer. I use following SQL query to get cancer related information from MAB table : SELECT * FROM MAB WHERE `GI_ID` ...
I have a query with subquery but subquery table has approximately 500.000 records and runing this query takes too long. How can I speed up this query? Any suggestions: SELECT ID, VehicleID, Plate, VehicleType, COALESCE( (SELECT EngineState FROM Locations WHERE Locations.VehicleID = Clients.VehicleID ORDER ...
I have a large table (~1M rows now, soon ~10M) that has two ranked columns (in addition to the regular data): avg_visited, a float 0-1 representing a %age popularity; higher is better alexa_rank, an integer 1-N giving an a priori ranking The a priori ranking is from external sources so can't be changed. Many rows have no popularity y...
I'm using php and need to do a bunch of select statements on the same table and column and get the rows that match those values. I have all the values I'm searching on in an array and right now I'm just looping and doing a select statement on each one separately. How could I put this all into one select statement and ditch the loop? Ri...
I need to have MySQL query like this one: UPDATE table_name SET 1 = 1 WHERE ID = 257 But I got the syntax error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = 1 WHERE ID = 257' at line 3 Need to perform an UPDATE query without u...
Hi Stackoverflow, I am writing a component and I have a scenerio of storing data into multiple tables with One-To-Many relationship. Master table is Student and Child table is Student_Subjects. One student can select multiple subjects. I want to store the data in Student_subject table with student. Data is submitted from one form whe...
I have a transaction table and I'm looking to generate a dataset to drive a line chart that shows the sum of all the sales happened on each day during a given period. I have never grouped results like that before and am scratching my head. Let's say the table is called "transactions", the "datetime" field is called timestamp, and the...
function fullJoinTest() { $con = ModelBase::getConnection(); $sql = "SELECT airline, airport FROM airlines LEFT JOIN airports on airlines.icao_code = airports.icao_code"; $query = $con->prepare($sql) or die("Error preparing sql in Search (test) "); $query->execute() or die("Error executing query i...
how to store the mysql query result into a local csv file ? edit 2 : because i dont have access in the remote machine . ...
I have a page that is displaying a company name and its details from a table A. Now say i have a company displayed,and its name is 'Company_one' now i want to have alphabetically sorted next company and previous company and their details etc. The data in my table is not sorted.Its stored as it gets the data. So now what kind of query...
Hello All, I have made a dump of my database in which one table is pretty huge (about 4 million records) and has good number of indices(One full text index along with a primary key and 3 unique indices). When i try to restore this dump the restore hangs(its been running for five days now) when it tries to execute the statement which ena...
Hi, can u please show me how to query 3 tables using *? thanks ...
I want to solve this issue in query @ for Ex : id ip 1 1.2.3.3 2 1.2.3.3 3 1.23.42.2 4 4.4.2.1 I am looking for ip count ids 1.2.3.3 2 1,2 1.23.42.2 1 3 4.4.2.1 1 4 I have tried with query - select count(id) ,ip , id from table group by ip ; it not working . how to solve this ....
I want to do the following query: SELECT getDistance(lat1, lng1, lat2, lng2) as `distance`, `forename`, `surname`, `etc..` WHERE `distance` < 10 ORDER BY `distance` ASC Obviously it won't work because you cannot do WHERE clauses on column 'alias'... can anyone help with an alternative?? getDistance() is a custom procedure... this ret...
I'd like to setup one instance of MySQL to flat-out reject certain types of queries. For instance, any JOINs not using an index should just fail and die and show up on the application stack trace, instead of running slow and showing up on the slow_query_log with no easy way to tie it back to the actual test case that caused it. Also, I'...
Hi all, This is the query which prints all the records from artist table and corresponding rating to that specific artist. And this query displays the rated artist at top and others after that. Now my prob is I have included rating table in this query to get the ratings of the artist rated by the members in the site. But how should ...