Hot answers tagged

3

You can really benefit from an index on hosts (hostname, scan_id) for this query, and possibly another one including status (especially for the second query below). Your query may also benefit from transferring some joins to per-row totals: CREATE INDEX idx_hostname_scanid ON hosts (hostname, scan_id); CREATE INDEX idx_hostname_status_scanid ON hosts ...


2

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent. MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5. With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some ...


2

Do you have 4 or more cores/processors machine? Current MySQL query cache implementation does not scale well and there is a mutex contention problem on systems with more thread (the query might spend more time waiting to check the query cache than it would need to be executed fresh if the relevant data are already in other buffers which allow parallel ...


2

I tried the LEFT JOIN .. IS NULL idea I noted in the comment: SELECT RECORD.ID AS RECORD_ID, POINT.ID AS POINT_ID FROM RECORD LEFT OUTER JOIN ( POINT LEFT JOIN SEGMENT s1 ON POINT.ID = s1.POINT_START LEFT JOIN SEGMENT s2 ON POINT.ID = s2.POINT_END ) ON RECORD.ID = POINT.RECORD_ID AND s1.POINT_START IS NULL AND s2.POINT_END IS ...


2

Does this work for you? I am testing in MySQL. SELECT LatestStock.* FROM daily_stock as LatestStock JOIN ( SELECT warehouse, product, MAX([date]) as MAXdate FROM daily_stock GROUP BY warehouse, product ) as LatestDate on LatestStock.warehouse = LatestDate.warehouse AND LatestStock.product = LatestDate.product AND LatestStock.date = ...


2

wait_timeout is a screwball one: "On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout. " max_connections claims to have an ...


1

If indexes exist on SEGMENT (POINT_START), on SEGMENT (POINT_END), and on POINT (RECORD_ID), your query would perform well. MySQL 5+ creates indexes automatically for any foreign keys, so they're most likely indexed. Note that IN is used for sets, but your query would return zero or one records at most for in IN clause, so an EXISTS would perform better: ...


1

The answer appears to be yes. Set members are accepted if they are separated by comma, however there should be no space between the comma and the next set element. Since commas are used to separate set members, columns must be separated by a different character. I have used tabs, and this works well. Performance is greatly improved (roughly by a factor of ...



Only top voted, non community-wiki answers of a minimum length are eligible