Jump to content

joeygallo

Members
  • Posts

    13
  • Joined

  • Last visited

Profile Information

  • Gender
    Not Telling

joeygallo's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Thanks. Method A is what we have now. I think it sounds like we just need some indexing done to the database. 50 million rows is basically a timeout. Indexing will likely fix it.
  2. Sorry for the long reply. There is a "WHERE". select COUNT (*) from ip_logs where ip = '123.123.123.123' IF FOUND: select field1, field2, field3 from ip_logs where ip = '123.123.123.123' In this query, I just need to know if it exists, not an actual count. If so, move on to the next query to get data from that row. So my question is.. what is the fastest possible method? I assume the two can be combined but my concern is speed.
  3. Also, the better answer would be select COUNT(*) from table where field1 = 'xyz'. There we have 50 million rows in the table but maybe not for the specific search. This is the primary reason COUNT is still needed.
  4. Not exactly. We still need to display the count number. We found X number of results.. Using mysql_num_rows is not an option with that many records.
  5. I need to COUNT then get data if rows exist. Something like this: $something = mysql_query("select COUNT(*) from table"); if (mysql_result($something,0) > 0) { $qry = mysql_query("select field1, field2, field3 from table"); $result = mysql_fetch_array($qry); echo $qry['field1']; // etc. } else { echo "no data found"; } Is the above the best way to do this or can the two queries be combined? I'm looking for the fastest possible method as we'll be dealing with a table that will eventually have 50 million+ rows. Thank you.
  6. Thank you to all contributors on this thread. I have the SQL query working as needed.
  7. I tried that. No more error but the output is not correct. Here's the current code. $get_totals = mysql_query("SELECT id, COUNT(id) AS total_count FROM ((SELECT id FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT id FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) as x GROUP BY id ORDER BY total_count DESC LIMIT 10"); if (mysql_num_rows($get_totals)) { while ($qry = mysql_fetch_array($get_totals)) { // display info here. // $qry['total_count'] is used to show number of entries. } } In the above output, the accounts are not ordered correctly and the value of the qry['total_count'] variable simply reads 1 for each entry found.
  8. I think I'm getting somewhere. I run this direct on the database (just basic queries with no extra stuff added): SELECT id FROM ((SELECT id FROM TABLE1) UNION (SELECT id FROM TABLE2)) I get this:
  9. This works: SELECT id, COUNT(id) AS total_count FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10 And this works: SELECT id, COUNT(id) AS total_count FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10 SELECT id, COUNT(id) AS total_count FROM ((SELECT id FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT id FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) GROUP BY id ORDER BY total_count DESC LIMIT 10 So independently they are working but together in the JOIN they are not. I tried select id, timestamp in both queries instead of just id. That didn't make a difference. I'm just not seeing what the problem is here.
  10. The UNION is not working. The tables don't have the same number of columns and I can't change that as one table has additional data than the other. EDIT: They do both have the ID and TIMESTAMP fields as mentioned before and they are both setup the same as far as the field types. Any suggestions on why this doesn't work?
  11. Trying to figure this out on my own I read this about UNIONS: TABLE1 has 33 columns, TABLE2 has only 30 columns. I will continue working on this.
  12. I replaced this: from TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime' With this as suggested: from ((SELECT * FROM TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime') UNION (SELECT * FROM TABLE2 where timestamp >= '$startTime' and timestamp <= '$endTime')) The output is this: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given The IDs are just user IDs. They are not indexed or auto-incremented.. just a numerical value. Both TABLE1 and TABLE2 have the timestamp and id fields in them. It's basically a matching table with a different name.
  13. I have a table that looks like this: TABLE1: id, username, timestamp 1, test1, 123123123 2, test2, 123123124 3, test3, 123123125 4, test4, 123123126 1, test1, 123123127 1, test1, 123123128 3, test3, 123123129 There are thousands of entries in that table. I'm producing a top 10 list of entries based on row count and date range using this: select id, COUNT(id) AS total_count from TABLE1 where timestamp >= '$startTime' and timestamp <= '$endTime' GROUP BY id ORDER BY total_count DESC LIMIT 10 startTime and endTime is defined and the above query works exactly how I want it to. Now I want to add another table into the mix. TABLE2: id, username, timestamp 1, test1, 123123123 2, test2, 123123124 3, test3, 123123125 4, test4, 123123126 How can I include TABLE2 into the query so the totals come from both tables and are combined?
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.