Jump to content

phpPeter

Members
  • Posts

    11
  • Joined

  • Last visited

phpPeter's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. He tried to you log via ssh on on his database server. Then, of course, will the queries executed locally. The issue is the latency of remote database connections.
  2. A remote database connection is always significant slower than a local connection. Typically, the network communication will generate much more overhead, so that the time will be reduced up to a relevant factor. It is caused by the -compared to memory speed- slow network connection, and the latency. A remote ssh is fast, because the communication between DB client and database happen locally. If there is the possibility of connection pooling, you should use that option. Establishing connections will produce signifcant delays. If you are lucky, this will reduce the query time very much, if multiple requests are issued against the database. A single request is not represantive in this case. Please run your test scripte multiple times, or create a test script, which will issue the request multiple times itself (I assume the most expensive operation is establishing the connection)
  3. It seems, it is required to define fulltext indices on the relevant columns (ALTER TABLE items ADD FULLTEXT `TSTIDX` ( `title` )... I think, indices of type "FULLTEXT" are not available on all types of tables (MyISAM seems to be okay, but InnoDB not). I prefer the conservative way using "LIKE". Bit more effort to write the SQL, but will work with all types of databases: WHERE title LIKE '%xbox%' OR description LIKE '%xbox%'
  4. You have to define the join on the user-table with JOIN users u ON ... Otherwise, the query will generate the cartesian product of the user-table and the other (joined) tables. This results in duplicates.
  5. phpPeter

    Select query

    I can't see duplicates. E.g. UVM 955 is listed twice, but one time for terminal status OFFLINE, another time for ONLINE. This seems to be correct. Maybe you can explain a bit more detailed, what you are trying to do.... BTW: Distinct is a bad idea in aggregating queries. You have to define the "group by" clause consistent to the grouping functions. Otherwise, it will not work. If you can explain more detailed, it's easier to solve your issue.
  6. Can you provide some sample data? Are there more than one cfm-values per fan-size? It would also help if you could provide the expected output.
  7. Just write a simple log-service, which will get the domain and/or IP as parameter from your script on the remote site. The log-server just logs each call into the database with the given domain and the current timestamp. Then you will have all infos you need. Include a simple call to the log service on your server (e.g. http://yourserver/log?IP=123.34.45.56), that's all.
  8. First, please clean up your queries and remove these senseless "group by"s. As long, as it is absolutely unclear, what the query should do at all, it is difficult to help you in detail. I will try to understand this: $res_all = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`"); $row_all = mysql_num_rows($res_all); should mean: --> SELECT count(*) as row_all FROM rishum WHERE status != "not relevant" and rishum_to=1 and aougust_form=? AND date BETWEEN ? AND ? $res_all_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='1' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`"); $row_all_PAY = mysql_num_rows($res_all_PAY); --> SELECT count(*) AS row_all_PAY FROM rishum WHERE status = "payed" and rishum_to=1 and aougust_form=? AND date BETWEEN ? AND ? $res_all2 = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`!='not_relevant' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`"); $row_all2 = mysql_num_rows($res_all2); --> SELECT count(*) AS row_all2 FROM rishum WHERE status != "not relevant" and rishum_to=2 and aougust_form=? AND date BETWEEN ? AND ? $res_all2_PAY = mysql_query("SELECT * FROM (SELECT * FROM `rishum` WHERE `status`='payed' AND `rishum_to`='2' AND `aougust_form`='".$_GET["aougust_form"]."' AND `date`>='".$filterByYear."/04/22' AND `date`<='".($filterByYear+1)."/04/21') as c GROUP BY `talmid_id`"); $row_all2_PAY = mysql_num_rows($res_all2_PAY); --> SELECT count(*) as row_all2_PAY FROM rishum WHERE status = "payed" and rishum_to=2 and aougust_form=? AND date BETWEEN ? AND ? ... So, first, you should rework your queries to my suggestions. This will improve your performance multiple times, because you don't have to transfer all datasets to count them afterwards, but this will do the query engine for you. However, I still haven't found out, what the "talmid_id" should be good for... Then, you can define the right indices. I think, you can leave it away with such a tiny table (the 11k records will be stored in the mem buffer after first query). But to be accurate, lets define the required index. My suggestion: CREATE INDEX i1 ON rishum(rishum_to, aougust_form, date, status); After that, it should be a matter of milliseconds... ;-)
  9. Your query just creating the cartesian product of both tables. Also, the where-statement is unclear, who should the query execution know, which column "skill" is meant: the one from table cautotest, or from table ctrail? I assume, you are looking for something like that: SELECT * FROM cautotest WHERE skill ='N' union SELECT * FROM ctrial WHERE skill ='N' ! But this statement will only work, if both table will have the same columns. If not, you have to name the columns instead of the "*"- Hope, it helps....
  10. Your query is performing a range scan. EXPLAIN select * FROM ip2country WHERE 3084727327 BETWEEN begin_long_ip AND end_long_ip; Let rearrange your query: 2 BETWEEN a AND b means: a <= 2 AND b >= 2 Your index consists out of two cols: a and b. So it will look like (a,b) (0, 0) (0, 1) (0, 2) (0, 3) (0, 4) (1, 0) (1, 1) (1, 2) (1, 3) (1, 4) (2, 0) (2, 1) (2, 2) (2, 3) (2, 4) (3, 0) (3, 1) (3, 2) (3, 3) (3, 4) (4, 0) (4, 1) (4, 2) (4, 3) (4, 4) An index can be queried top down. It must run down to find all matching entries and can stop at (3,0), because here a will be greater 2. I don't know, if it is supported by mySQL, but try following index: CREATE INDEX i2 ON ip2country(begin_long_ip,end_long_ip DESC); This will reduce the range for the scan. If no descending sorted columns are possible in mySQL, it is getting difficult. Then you should explain more detailed, what's the idea behind the query to figure out some workarounds.
  11. The runtime on mySQL seems to be very long. Inserting just 10k rows should not take so long. I have no experience with SP on mySQL in particular. However, it seems to be not very performant. I assume, there are no triggers on the table. Maybe you can simplify the script (just insert 10k constant rows into the db). I can't imagine, but maybe some of the functions or statements are very imperformant.
×
×
  • 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.